Excel VBA, select a block of row down

Question

I have a sub like this:

 Sub deleteExcessData(selectSheet As String, selectRow As Integer)   
    'selectSheet = go to a certain sheet
    'selectRow = go to a certain row

 End Sub

What I want to do with this is select a row to start, then select all the non-blank rows below that row all the way to the bottom, then delete that selection. (this can include the row or not include that row)

I only want to use 2 variables in this sub, one is to select the sheets and on is used to select the anchor row. Data is contiguous.

What if there are some random blank cells in the data?

Any suggestion?

A modern Excel worksheet has 1,048,576 rows. Change selectRow As Integer to selectRow As Long. — user4039065
– user4039065, Commented Jul 7, 2015 at 17:51

nicholas79171 · Accepted Answer · 2015-07-07 17:57:13Z

1

Try something like this:

Sub deleteExcessData(selectSheet As String, selectRow As Integer)
    lastRow = Worksheets(selectSheet).Cells(1, 1).End(xlDown).Row
    Worksheets(selectSheet).Rows(selectRow & ":" & lastRow).Delete
End Sub

If you need to go up from the bottom, try this:

Sub deleteExcessData(selectSheet As String, selectRow As Integer)
    lastRow = Worksheets(selectSheet).Cell(Row.Count, 1).End(xlUp).Row
    Worksheets(selectSheet).Rows(selectRow & ":" & lastRow).Delete
End Sub

edited Jul 7, 2015 at 17:57

answered Jul 7, 2015 at 17:44

nicholas79171

1,2832 gold badges18 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

nicholas79171 Over a year ago

And be sure to try this out on some test data first.

knt5784 Over a year ago

how should I do it if there are a few blank cells in the data?

user4039065 Over a year ago

I believe your first sub only deletes row 1,048,576. You have not extended the range reference from the start to the bottom.

knt5784 Over a year ago

So I tried your code (the first part) with this: Call deleteExcessData("data", 5) and it gives me "object does not support this property or method". And your second code gives me "object required" on the second row. I already declared a global lastRow variable as Long.

nicholas79171 Over a year ago

Does it point you to a line where the error occurs? Also be sure to try @Jeeped's answer.

|

user4039065 · Accepted Answer · 2015-07-07 18:01:26Z

0

This should remove all rows to the bottom of the worksheet including the row being called.

Sub test()
    Call deleteExcessData("Sheet1", 9)
End Sub

Sub deleteExcessData(selectSheet As String, selectRow As Long)
    With Sheets(selectSheet).Cells(selectRow, 1)
        .Resize(Rows.Count - selectRow + 1, 1).EntireRow.Delete
    End With
End Sub

The above removes all rows from 9:1048576.

You could alternately pass across the worksheet object rather than the name if that is already defined by the calling sub.

answered Jul 7, 2015 at 18:01

user4039065

Collectives™ on Stack Overflow

Excel VBA, select a block of row down

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related