Not getting values of all rows after auto filtering with for loop

Question

I am struggling for writing the code - below query please help any one on writing it.

  TestDataSheetName = ActiveWorkbook.Worksheets(x).Name
  ActiveWorkbook.Worksheets(x).Activate


CountTestData = ActiveWorkbook.Worksheets(x).Range("A" & Rows.Count).End(xlUp).Row
Range("A10").Select
Range("A10").AutoFilter
Selection.AutoFilter Field:=14, Criteria1:=">=" & DateToday


ActiveWorkbook.Worksheets(x).Activate
CountTestDataAftFilter = ActiveWorkbook.Worksheets(x).Range("A1", Range("A65536").End(xlUp)).SpecialCells(xlCellTypeVisible).Count


MsgBox CountTestDataAftFilter     
For w = 10 To CountTestDataAftFilter

      Set Foundcell1 = ActiveWorkbook.Worksheets(x).Cells.Find(What:=DateToday, After:=[ActiveCell], _
                    SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    LookIn:=xlValues, LookAt:=xlPart, MatchCase:=True)                     
Next

' after filtering with today's date i got 5 rows with today's date and i have written for loop for getting all row values but after finding first row then it is not finding the second row value and it is again start with first row

Please help me on above code.

Thanks&Regards, Basha

score 0 · Answer 1 · answered Oct 21 '13 at 05:15

You're looking for the .FindNext function. Try something like this: (Please note, you may need to modify this code slightly to fit your particular case.)

Sub UseFindNext()

Dim TestDataSheet As Worksheet
Dim FoundCell1 As Range
Dim DateToday As Date
Dim firstAddress As String
Dim x As Long
Dim CountTestData As Long
Dim CountTestDataAftFilter As Long

x = 1

Set TestDataSheet = ActiveWorkbook.Worksheets(x)

CountTestData = TestDataSheet.Range("A" & Rows.count).End(xlUp).Row
Range("A10").AutoFilter Field:=14, Criteria1:=">=" & DateToday

CountTestDataAftFilter = TestDataSheet.Range("A1", Rows.count).End(xlUp)).SpecialCells(xlCellTypeVisible).count

Set FoundCell1 = TestDataSheet.Cells.Find(What:=DateToday, After:=TestDataSheet.Range("A10"), _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        LookIn:=xlValues, LookAt:=xlPart, MatchCase:=True)
firstAddress = FoundCell1.Address
Do
    'Do whatever you're looking to do with each cell here. For example:
    Debug.Print FoundCell1.Value
Loop While Not FoundCell1 Is Nothing And FoundCell1.Address <> firstAddress

End Sub

Appologies for late responding – Mdbasha Lavangiri Feb 02 '14 at 07:32 — Mdbasha Lavangiri, Feb 02 '14 at 07:32
Thanks for every one i got the solution – Mdbasha Lavangiri Feb 02 '14 at 07:33 — Mdbasha Lavangiri, Feb 02 '14 at 07:33

score 0 · Answer 2 · edited May 23 '17 at 11:50

I don't know why you have to go through each value.
You already used AutoFilter to get the data you want.

But here's another approach that might work for you.

Sub test()

Dim ws As Worksheet
Dim wb As Workbook
Dim DateToday As String 'i declared it as string for the filtering
Dim rng, cel As Range
Dim lrow As Long

Set wb = ThisWorkbook
Set ws = wb.Sheets(x)

DateToday = "Put here whatever data you want" 'put value on your variable

With ws
    lrow = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("N10:N" & lrow).AutoFilter Field:=1, Criteria1:=DateToday
    'I used offset here based on the assumption that your data has headers.
    Set rng = .Range("N10:N" & lrow).Offset(1, 0).SpecialCells(xlCellTypeVisible)
    'here you can manipulate the each cell values of the currently filtered range
    For Each cel In rng
        cel.EntireRow 'use .EntireRow to get all the data in the row and do your stuff
    Next cel
    .AutoFilterMode = False
End With

End Sub

BTW, this is based on this post which you might want to check as well to improve coding.
It is a good read. Hope this helps.

Not getting values of all rows after auto filtering with for loop

2 Answers2