0

Code below was working. Can't seem to figure out what changed. It errors out at

Firstrow = .UsedRange.Offset(1).Row

Application defined or object defined error.

First row shows 0 in the highlighted code.

The sheet has a query which pulls in data in a data table.

I've even tried using the ListObjects(1).Unlist to see if it had a problem with the table but still error.

Any assist is appreciated.

 ```Sub Delete_Rows_Individual()

    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long
    Dim rng As Range


    With ActiveSheet


        .Select

        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView



        'Set the first and last row to loop through
        Firstrow = .UsedRange.Offset(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

        'Loop from Lastrow to Firstrow (bottom to top)
        For Lrow = Lastrow To Firstrow Step -1

            'Check the values in the A column
            With .Cells(Lrow, "A")

                If Not IsError(.Value) Then

                    If .Value <> Worksheets("Variables").Range("B12") Then


                        If rng Is Nothing Then
                            Set rng = .Cells
                        Else
                            Set rng = Application.Union(rng, .Cells)
                        End If
                    End If

                End If
            End With

        Next Lrow

    End With

    'Delete all rows at once
    If Not rng Is Nothing Then rng.EntireRow.Delete

    ActiveWindow.View = ViewMode
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With




End Sub```

UPDATE: The above code works in other files that are basically the same. They use data tables, use the exact same code, but do not error out. Don't know if this helps but wanted to include.

Bill Littlejohn
  • 9
  • 5
  • What's the result if you `Debug.Print .UsedRange.Address`? – BigBen Nov 21 '19 at 16:14
  • $A:$AE is the response – Bill Littlejohn Nov 21 '19 at 16:18
  • Then `UsedRange` spans the entire column, so you can't offset a row. – BigBen Nov 21 '19 at 16:19
  • https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba/11169920#11169920 – Siddharth Rout Nov 21 '19 at 16:22
  • What would be the proper way to correct it? – Bill Littlejohn Nov 21 '19 at 16:22
  • lol posted at same time I will read that thank you – Bill Littlejohn Nov 21 '19 at 16:23
  • So I replaced ```Firstrow = .UsedRange.Offset(1).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row``` with ```With ActiveSheet Lastrow = .Range("A" & .Rows.Count).End(xlUp).Row End With``` Now gets same error at ```With .Cells(Lrow, "A")``` – Bill Littlejohn Nov 21 '19 at 16:30
  • Bill that is becuase `Lastrow` and `Lrow` are two different variables? ;) – Siddharth Rout Nov 21 '19 at 16:58
  • So intitially, I used the offset to keep from deleting the header row. The macro is part of a series. This macro is meant to go tab by tab and delete any row that does not have a name that is defined in the beginning of the loop deleted, This is a one button click macro to run a series of actions so as not to have to do any of the steps manually. – Bill Littlejohn Nov 21 '19 at 16:58
  • @SiddharthRout lrow is part of the for loop. Confusing but that is what it is – Andreas Nov 21 '19 at 17:00
  • Ah I see @Andrea's. Bill what is the value of lrow at the time of error? I believe it is `0` because of `firstrow` – Siddharth Rout Nov 21 '19 at 17:03
  • @SiddharthRout yes you are correct, it is 0 – Bill Littlejohn Nov 21 '19 at 17:09
  • 1
    @Andreas you can use the column letter within `Cells`, yes. – BigBen Nov 21 '19 at 17:11
  • @SiddharthRout However, I replaced the Firstrow code with ```With ActiveSheet Lastrow = .Range("A" & .Rows.Count).End(xlUp).Row End With``` – Bill Littlejohn Nov 21 '19 at 17:24
  • Ok.. forget everything for a moment. Tell me what exactly are you trying to do? What exactly is your first row and which column is it in? Col A? Is your first row not `1`? – Siddharth Rout Nov 22 '19 at 02:51
  • I have a data table that needs to have all data except a specific name deleted. The entire process loops through a list of names making individual files from each name in the list. The specific code above was deleting any row where column A did not match the name that was needed. – Bill Littlejohn Nov 22 '19 at 17:32

0 Answers0