Keep getting error 91...

[HaakLord]

Hi, I'm trying to make a short macro to copy all rows with data in a certain column to a new sheet but every time I run it I get the error

"Run-time error '91':
Object variable or With block variable not set"

When I debug it is the totalRows variable that is highlighted.

I don't have the issue when I use the same variable in a different macro. What am I doing wrong???

Sub filteredPayables()




Sheets.Add after:=Sheets(1)




Dim x As Long
Dim totalRows As Long


x = 1


totalRows = Cells.Find(What:="*", _
                 after:=Range("A1"), _
                 LookAt:=xlPart, _
                 LookIn:=xlFormulas, _
                 SearchOrder:=xlByRows, _
                 SearchDirection:=xlPrevious, _
                 MatchCase:=False).Row


Do While x < totalRows


             If Range(x, 6) <> 0 Then


                     ActiveWorkbook.Sheets(1).Rows(x).EntireRow.Copy
                     ActiveWorkbook.Sheets(2).Range("A1").End(xlDown).Offset(1, 0).EntireRow.Paste
                     x = x + 1
        
             Else
                     x = x + 1
        
             End If
        


Loop






End Sub

 

[HaakLord]

Range("A1") is the first row in Excel.
Cells(Rows.Count,"A") is the last row in Excel.

People use to use Range("A65536") for last row in Excel years ago. But when "xlsx" files came out, they now have over 1 million possible rows. So the maxmum row number varies, depending on which version of Excel you are using. Using "Rows.Count" will find the last row in Excel, regardless of which version you are using.

The logic in the two method is different. One starts from the top, the other the bottom.
The issue with starting form the top is the "xlDown" looks for first blank row after the last row of data below it. But if there is no data below it (i.e. the sheet has no data, or only data in row 1), it will do all the way down to the bottom of the sheet. Now, if you try to move down more row, you get errors, as you would be moving off of the sheet.

The other method, starting from the bottom using "xlUp", will go up until it finds a row of data (which would be your last row of data). If it finds no data, it stops at row 1. Moving down one row from row 1 is not an issue (that would be row 2).

Because of this situation, it usually works better to find the first empty row of data by working up from the bottom rather than going down from the top.

Fantastic explanation, thank you!

 

[Joe4]

You are welcome.

Note that because of what I explained, if you do have no data on your sheet, it will skip the first row and paste it on the second row (leaving a blank row in row 1). Not a big deal, just something to be aware of.
If you start off with headers in row 1, that won't be a concern at all.

 

[HaakLord]

You are welcome.

Note that because of what I explained, if you do have no data on your sheet, it will skip the first row and paste it on the second row (leaving a blank row in row 1). Not a big deal, just something to be aware of.
If you start off with headers in row 1, that won't be a concern at all.

Yup I did end up using headers so that was perfect.