How do I find a blank row and delete it?

L

lpdarspe

I import data from a source and it has 1 blank row between rows of data and
notes. I need a routine that will find that blank row and delete it and move
the remainder of the information up.
 
S

sebastienm

Hi,
Assuming you determine blank rows by capturing all blank cells in column A:

Sub test()
Dim rg As Range, rgBlank As Range
'-------- CHANGE HERE -----------
Set rg = ActiveSheet.Range("A:A")
'--------------------------------

'get blank cells from rg
On Error Resume Next
Set rgBlank = rg.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If rgBlank Is Nothing Then 'no blank cell
MsgBox "No Blank cells found"
Else 'else delete entire rows
rgBlank.EntireRow.Delete
End If
End Sub
 
W

wangww

Sub DeleteRows()
ActiveSheet.UsedRange.Select
X = ActiveSheet.UsedRange.Columns.Count
Selection.AutoFilter
For i = 1 To X
Selection.AutoFilter Field:=i, Criteria1:="="
Next
X = ActiveSheet.UsedRange.Count
Range("A2:G" & X & "").Delete
End Sub
 
Top