Keep getting error 91...

HaakLord

New Member
Joined
Sep 1, 2016
Messages
18
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???


Code:
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
 
Last edited by a moderator:

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
That line of code will look at the activesheet, which in your case is the sheet that has just been added.
As it's a new sheet it is totally empty & so the Cells.Find cannot anything.
 
Upvote 0
Ah okay that makes sense. So now when I run it I get "Application-defined or object-defined error" on this line:

ActiveWorkbook.Sheets(2).Range("A1").End(xlDown).Offset(1, 0).EntireRow.Paste

Is there something wrong with this?

Sorry this is my first time trying to write a macro...
 
Upvote 0
ActiveWorkbook.Sheets(2).Range("A1").End(xlDown).Offset(1, 0).EntireRow.Paste
If there is no data below cell A1 on the sheet, then the ".End(xlDown)" part will go down to the very last possible row on your sheet.
Then the "Offset(1, 0)" will try to move down one more row. But if you are already in the last possible row in Excel, it is impossible to move down any farther. Hence, the error.

Try this instead:
Code:
[COLOR=#333333]ActiveWorkbook.Sheets(2).Cells(Rows.Count,"A").End(xlUp).Offset(1, 0).EntireRow.Paste[/COLOR]
This starts at the bottom, and goes up until it finds a row of data.
 
Upvote 0
The code has some details, try this:


Code:
Sub filteredPayables()
    Dim x As Long, totalRows As Long
    
    x = 1
    totalRows = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
    Sheets.Add after:=Sheets(1)
    Do While x <= totalRows
        If [COLOR=#0000ff]Sheets(1).Cells(x, 6)[/COLOR] <> 0 Then
            Sheets(1).Rows(x).EntireRow.Copy [COLOR=#0000ff]Sheets(2).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)[/COLOR]
        End If
        x = x + 1
    Loop
End Sub
 
Upvote 0
Because there is not data on sheets(2) (the new sheet) this
Code:
Range("A1").End(xlDown)
will take you to the vary last row in the sheet. You are then trying to go down 1 cell, which you can't as its already the last row.
 
Upvote 0
How about
Code:
Sub HaakLord()
   Dim Ws As Worksheet
   
   Set Ws = Sheets(1)
   Sheets.Add , Sheets(1)
   Ws.Range("F:F").AutoFilter 1, "<>0"
   Ws.AutoFilter.Range.EntireRow.Copy Sheets(2).Range("a1")
   Ws.AutoFilterMode = False
End Sub
 
Upvote 0
If you are going to filter column F, then

Code:
Sub Macro4()
    With Sheets(1).Range("F1:F" & Sheets(1).Range("F" & Rows.Count).End(xlUp).Row)
        .AutoFilter Field:=1, Criteria1:="<>", Operator:=xlAnd, Criteria2:="<>0"
        .EntireRow.Copy
        Sheets.Add after:=Sheets(1)
        ActiveSheet.Paste
    End With
End Sub
 
Upvote 0
If there is no data below cell A1 on the sheet, then the ".End(xlDown)" part will go down to the very last possible row on your sheet.
Then the "Offset(1, 0)" will try to move down one more row. But if you are already in the last possible row in Excel, it is impossible to move down any farther. Hence, the error.

Try this instead:
Code:
[COLOR=#333333]ActiveWorkbook.Sheets(2).Cells(Rows.Count,"A").End(xlUp).Offset(1, 0).EntireRow.Paste[/COLOR]
This starts at the bottom, and goes up until it finds a row of data.

Thank you this worked! What is the difference in using Range("A1") vs Cells(Rows.Count,"A") ?
 
Upvote 0
What is the difference in using Range("A1") vs Cells(Rows.Count,"A")
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.
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top