How do I find a blank row and delete it?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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
 
Back
Top