however, Did you notice something glaringly wrong with what I put up?
Possibly a few (whether glaring or not depends on the experience of the viewer!).
I just don't know why it would be throwing any kind of error.
Always useful for us to know what the error was.
It's supposed to select all blank cells and just shift up to condense all the data.
Just confirm that you're
not seeking to retain row integrity, that is data on any given row stays together on a row. It's unusual if you don't, but come back and I'll cater for it. I suspect you
do want to retain row integrity so I'll stick with that at the moment.
When debugging/developing, it can be useful temporarilyto intersperse your code with some extra lines; I often use
.Select so that I can be sure that what I think my code is referring to, actually is (this needs you to step through the code one line at a time with
F8). So in yor code I might add the following red lines:
Sub quickClean()
Dim lastRow As Long
With ActiveSheet
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
Range("A" & lastRow).Select 'you already put this one in.. but notice it's a little different from below where you have 'A1' instead of just 'A'.
Dim rng As Range
Range("A1" & lastRow).Select 'the same as below.
Set rng = Range("A1" & lastRow).SpecialCells(xlCellTypeBlanks)
rng.Select
rng.Rows.Select
rng.Rows.Delete Shift:=xlShiftUp
End Sub
The first red line above may surprise you with what it selects; let's say it previously found
lastRow to be
500, what it will select is Range("A1" &
500), which is Range("A1
500"), a single cell some 1000 rows below the
lastRow!
The fact that it's a single cell is important when it comes to the
SpecialCells operation;
SpecialCells will assume you want
SpecialCells from the entire sheet!
The code you supplied implies (from
rng.rows.delete) that you only want to find blanks in column A and delete the rows that they're on, and I'm guessing complete rows from the table or even entire rows on the sheet - you'll have to fill us in.
So this is a guess:
Code:
Sub quickClean2()
Dim lastRow As Long
With ActiveSheet
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
Range("A2:A" & lastRow).Select 'can delete later
Dim rng As Range
Set rng = Range("A2:A" & lastRow).SpecialCells(xlCellTypeBlanks)
rng.Select 'can delete later
rng.EntireRow.Select 'can delete later
rng.EntireRow.Delete
End Sub
A couple more things to note:
1. You used
With ActiveSheet; the
End With could be placed a little lower in the code - it would make the code a little more robust. (Actually it makes very little difference. One instance where it would foul up is if this code is in a sheet's code-module which is not the active sheet's code-module.)
2. It might be that column A has no blank cells at all, in which case the
SpecialCells operation will throw an error which you might want to cater for.
Both these points addressed below:
Code:
Sub quickClean3()
Dim lastRow As Long, rng As Range
With ActiveSheet
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
On Error Resume Next 'next line might error.
Set rng = .Range("A2:A" & lastRow).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0 'restore normal error operations.
If Not rng Is Nothing Then rng.EntireRow.Delete
End With
End Sub
