Find Blank Row and delete all records below it

[serhat]

I searched the archives but couldnt find exactly what I was lookin
for.

I run a macro that does a lot of sorting and subtotaling. Once it i
all done I know that in Column A all the data after the first blan
cell is junk.

I would like the macro to search for a blank cell in column A and the
delete all rows below it including it. By all rows it could be fro
current row till row 65536 or it could be from the blank cell till th
end of data in column B.

I would appreciate anyones help.

Thanks!
S

 

[Bob Phillips]

SK,

here is one way

Dim cFirst As Long
Dim cLast As Long
cFirst = Columns("A:A").SpecialCells(xlCellTypeBlanks).Row
cLast = Cells(Rows.Count, "A").End(xlUp).Row
Rows(CStr(cFirst) & ":" & CStr(cLast)).Delete


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[William]

Hi SK

Sub test()
Dim r As Range
With ActiveSheet
Set r = .Range("A:A").SpecialCells(xlCellTypeBlanks).Cells(1)
Set r = .Range(r, .Range("A" & Rows.Count).End(xlUp))
r.EntireRow.Delete
End With
End Sub

--
XL2002
Regards

William

(e-mail address removed)

| I searched the archives but couldnt find exactly what I was looking
| for.
|
| I run a macro that does a lot of sorting and subtotaling. Once it is
| all done I know that in Column A all the data after the first blank
| cell is junk.
|
| I would like the macro to search for a blank cell in column A and then
| delete all rows below it including it. By all rows it could be from
| current row till row 65536 or it could be from the blank cell till the
| end of data in column B.
|
| I would appreciate anyones help.
|
| Thanks!
| SK
|
|
| ---
| Message posted
|

 

[William]

Hi SK

My original post would fail if there are no blank cells between call A1 and
the last cell in column A. This accounts for that possibility.

Sub test()
With ActiveSheet
If Application.CountA(.Range("A:A")) + 1 = _
..Range("A:A").SpecialCells(xlCellTypeBlanks).Cells(1).Row Then Exit Sub
..Range(.Range("A:A").SpecialCells(xlCellTypeBlanks).Cells(1), _
..Range("A" & Rows.Count).End(xlUp)).EntireRow.Delete
End With
End Sub

--
XL2002
Regards

William

(e-mail address removed)

| Hi SK
|
| Sub test()
| Dim r As Range
| With ActiveSheet
| Set r = .Range("A:A").SpecialCells(xlCellTypeBlanks).Cells(1)
| Set r = .Range(r, .Range("A" & Rows.Count).End(xlUp))
| r.EntireRow.Delete
| End With
| End Sub
|
| --
| XL2002
| Regards
|
| William
|
| (e-mail address removed)
|
| | | I searched the archives but couldnt find exactly what I was looking
| | for.
| |
| | I run a macro that does a lot of sorting and subtotaling. Once it is
| | all done I know that in Column A all the data after the first blank
| | cell is junk.
| |
| | I would like the macro to search for a blank cell in column A and then
| | delete all rows below it including it. By all rows it could be from
| | current row till row 65536 or it could be from the blank cell till the
| | end of data in column B.
| |
| | I would appreciate anyones help.
| |
| | Thanks!
| | SK
| |
| |
| | ---
| | Message posted
| |
|
|