VB Code to Delete Unused Rows

[Ken]

Excel 2000 ... No response to an earlier post, but turns
out I didn't have issue correct either.

I have a Template consisting of 15003 rows ... The last
row which might contain data is 15000 ... with row 15001
being empty ... & 15002 & 15003 containing various totals.

I copy/paste in a random amount of data (2000-11000
records & then run a recorded Macro to format data.

Issue is ... I would like VB Code to place near end of
Macro that would delete all unused rows between end of
data & row 15000 (while keeping rows 15001, 15002 & 15003).

The following VB Code worked fine until I added the Totals
to rows 15002 & 15003 ... These rows now get deleted (so
goes the Totals) ... I wish to keep them.

cLastRow = Cells(Rows.Count, "B").End(xlUp).Row
Set myRng = Range("B" & cLastRow + 1 & ":B15000")
myRng.EntireRow.Delete

Note: Col A is Blank ... Col B contains data

Above said ... can one of you many Wizards of this board
tell me what adjustments to above code I must make ...
or ... offer me new code all together to handle this???

My many thanks to those of you that support these
boards ... Kha

 

[Tom Ogilvy]

cLastRow = Cells(15001, "B").End(xlUp).Row
Set myRng = Range("B" & cLastRow + 1 & ":B15000")
myRng.EntireRow.Delete

 

[Guest]

This code should work if all of the rows with data have something entered in
column A. If not, use a column that would have data in every row. If not the
first
worksheet, substitute sheet name for (1).


Dim x As Integer

x = 2000 'counter for rows beginning with row 2000

Sheets(1).Unprotect 'Include if sheet is protected

'Loops through rows. If cell in column A is blank, select row and delete
Do While x < 15001
If Sheets(1).Cells(x, 1).Value = "" Then
Cells(x, 1).Select
Selection.EntireRow.Delete
x = x + 1
Else
x = x + 1
End If

Loop

Sheets(1).Protect 'Use if you want sheet protected