Thread: Excel VBA filtering assistance

Hello,

I'm new to the forum and pretty new to excel VBA. I have a large spreadsheet and would like to filter it.

The spreadsheet has many columns, although only one I want to use for sorting. It is an integer value (Availability) and every time it changes compared to the previous row i'd like to record the change (i.e. copy the whole row with the new Availability figure and the previous row with the old Availability figure into a new spreadsheet).

I can run an autofilter, but now I don't know if there is a way I can access the row index of the filtered results easily in VBA? If so, that would make things easier.. Or any other suggestions of alternative approaches?

Would be very grateful for any assistance.

Many thanks,

tonypanda

It would be good if you attach a sample file. Would only the availability column change or would other columns change?

Ok, thanks. Example attached of what I'd like to do. Attachment 137682 hope this makes it clear!

All the other columns will be different, but I do need to retain that information - for example the date and time when the availability changed.

Hey Tonypanda,

Use this code -

Option Explicit
Dim lrow As Long
Dim i As Long

Sub compare_rows()

lrow = Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row
Worksheets(1).Rows("1:1").Copy Worksheets(2).Range("A1")
For i = 2 To lrow - 1
    If Worksheets(1).Range("C" & i).Value <> Worksheets(1).Range("C" & i + 1).Value Then
        Worksheets(1).Rows(i & ":" & i + 1).Copy Worksheets(2).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    End If
Next i
End Sub