VB Code to Delete Unused Rows
 

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

VB Code to Delete Unused Rows
 

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


--
Regards,
Tom Ogilvy

"Ken" wrote in message
...
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

VB Code to Delete Unused Rows
 

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

"Ken" wrote:

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