PDA

View Full Version : Delete rows based on dynamic data range



msquared99
04-05-2012, 02:49 PM
OK, I'm new at this. I have several reports that I run. I have some code that finds the last empty row in the sheet. I want to use this value to have all the rows from this row on to be deleted. The rows of data will vary in the numbers of rows it has.


Here is the code that finds the first row with no data:
lastRow = Worksheets("M3").Range("B" & Rows.Count).End(xlUp).Row + 1


I want this to use the value of lastRow and delete or clear the contents to the end of the workbook:
Worksheets("M3").Range(lastRow).End(xlDown).Select.ClearContents

Workbook 1 the data ends on say row 44, delete or clear from row 45 to the bottom of the workbook.

Workbook 2 may have the data end on row 100, it varies in each workbook.

Thanks for the help.

Mike

CatDaddy
04-05-2012, 03:03 PM
something like this might be what you want? (for all open workbooks
Sub test()
Dim wb as Workbook

For each wb in Application.Workbooks
wb.Activate
lastRow = Worksheets("M3").Range("B" & Rows.Count).End(xlUp).Row + 1
Worksheets("M3").Range(lastRow).End(xlDown).Select
Selection.ClearContents

Next wb

End Sub

msquared99
04-06-2012, 06:31 AM
Tried it and and get a run-time error 1004 on the line: Worksheets(:M3").Range(lastRow).End(xlDown.Select

Sub Macro2()
Dim lastRow As Long
Dim wb As Workbook
For Each wb In Application.Workbooks
wb.Activate
Range(Selection, Selection.End(xlToRight)).Select
Range("B3:N137").Select
Selection.Sort Key1:=Range("B4"), Order1:=xlAscending, Key2:=Range("F4") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False _
, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:= _
xlSortNormal

lastRow = Worksheets("M3").Range("B" & Rows.Count).End(xlUp).Row + 1

Worksheets("M3").Range(lastRow).End(xlDown).Select
Selection.ClearContents

Next wb

End Sub

Aussiebear
04-06-2012, 07:46 AM
Mike, what does this line do?
Range(Selection, Selection.End(xlToRight)).Select
given that you then select the range B3:N137 in the very next line?

msquared99
04-06-2012, 10:35 AM
Here is what I ended up with and it works perfect:

Worksheets("M3").Range("B3").Select

lastRow = Worksheets("M3").Range("B" & Rows.Count).End(xlUp).Row + 1

Worksheets("M3").Range("B" & lastRow).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete

.Close savechanges:=True

Thanks for everyones input.

Mike