VBA Range.End(xlDown) stops at last visible row-CodePudding

I am doing a simple VBA script in Microsoft Excel which iterates a list of cars and a list of information about when the cars were refueled to provide an overview of how many kilometers each car is driving each month.

I make use of the Range.End property to calculate the number of rows with data and then loop through the indicies.

Set Data = Worksheets("Tankninger") ' Danish for refuellings
NumRows = Data.Range("A1", Data.Range("A1").End(xlDown)).Rows.Count
    
For x = 1 To NumRows
    ' Process data
Next

Everything seemed to be working fine, however I found that if someone applied a filter to e.g. the sheet with refuelling data - e.g. only showing data related to car A, then NumRows would be assigned the index of the last visible row.

Example: if the refuling sheet contains 100 records and the records related car A are located on row 50-60, then NumRows would be assigned the value 60 - resulting in my script ignoring the last 40 records.

Is there a way to make the Range.End property ignore any filter applied to sheet, or will I have to change the implementation to use a while-loop instead?

CodePudding user response：

I ended up replacing the for-loop with an while-loop. This allowed med to access every cell regardless of any filtering applied to the sheets.

Set Data = Worksheets("Tankninger") ' Danish for refuellings
r = 2
    
While Not IsEmpty(Cars.Cells(r, 1).value)
    ' Process data
Wend

CodePudding user response：

What you can do is add the following in your code to remove filters before you find the last row with data.

  'Remove all filters
    Worksheets("Sheet1").Activate

     On Error Resume Next
     ActiveSheet.ShowAllData