How do I find a blank row and delete it?
 

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.

How do I find a blank row and delete it?
 

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

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"lpdarspe" wrote:

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.

How do I find a blank row and delete it?
 

Sébastien, Thank you very much! It did what I needed!

"sebastienm" wrote:

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

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"lpdarspe" wrote:

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.

How do I find a blank row and delete it?
 

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

"lpdarspe" wrote:

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.