How to write a macro to filter a column and take out the required value?

Question

I need a macro that need to filter a column and to take out the required date value along with the cell position (i.e say "4/22/2018" cell position "A9 or just 9"). Kindly help me out to fix this issue

See the code that I wrote below

Dim Date As String

Date = Sheets("alldata")
Rows("3:3").Select.AutoFilter.Range("$A$3:$AA$606").AutoFilter , Field:=1, Criterial:="#VALUE!"
Range("A3").Select.xlFilterValues.offset(1, 0).Copy.value

Sheets("Log").Cells(2, "AF").value = Date

This syntax `Rows("3:3").Select.AutoFilter` and also your next line is not valid. I recommend to read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). And always tell whats wrong with your code. Were do you get errors and which. — Pᴇʜ, Apr 17 '18 at 09:36
Also `Dim Date As String` Date is a reserved word. you can use it like this — Siddharth Rout, Apr 17 '18 at 09:39
Too many errors... Is `Criterial:` a typo? It should be `Criteria1` — Siddharth Rout, Apr 17 '18 at 09:41
Also `String` cannot be a Sheet `Date = Sheets("alldata")` if you define the variable as `String`. — Pᴇʜ, Apr 17 '18 at 09:42
If your dates are in Col A then how can the date position be in Col W? `say "4/14/2017" cell position "W7"...` Please fix your question before we can give you an appropriate reply... — Siddharth Rout, Apr 17 '18 at 09:42
The code is running but the date value is not copied, instead leaving it as a blank cell. — rozario_k, Apr 17 '18 at 09:44
It is not possible that the code that you posted above is working. it will not even run. It has so many errors — Siddharth Rout, Apr 17 '18 at 09:45
Yeah, i accept there are error and I am not having much knowledge in writing macros. so guys please help me with some simple code to extract the info that i needed. — rozario_k, Apr 17 '18 at 09:49
Do you want to filter a date and return the row number or the address of that cell? — Siddharth Rout, Apr 17 '18 at 09:53
Yes, Please see the picture i attached hope it will give you a clear view on what I need. 1. I need to uncheck "#value!" by applying filters to date column 2. Then, date values will be available. In that i need the first date value and the row number of the first date value. — rozario_k, Apr 17 '18 at 09:59

Siddharth Rout · Accepted Answer · 2018-04-17T10:27:34.503

Is this what you are trying?

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range

    Set ws = Sheets("alldata")

    With ws
        Set rng = .Range("$A$3:$A$606")

        '~~> Remove any filters
        .AutoFilterMode = False

        With rng
            .AutoFilter Field:=1, Criteria1:="<>#VALUE!"

            '~~> Get the Row Number
            MsgBox .Offset(1, 0).SpecialCells(xlCellTypeVisible).Row

            '~~> Get The cell Address
            MsgBox .Offset(1, 0).SpecialCells(xlCellTypeVisible).Cells(1, 1).Address

            '~~> Get the Date
            Sheets("Log").Cells(2, "AF").Value = _
            .Offset(1, 0).SpecialCells(xlCellTypeVisible).Cells(1, 1).Value
        End With

        '~~> Remove any filters
        .AutoFilterMode = False
    End With
End Sub

Yes the same. Thanks a lot. – rozario_k Apr 17 '18 at 11:35 — rozario_k, Apr 17 '18 at 11:35

score 0 · Answer 2 · answered Apr 17 '18 at 10:00

The following will filter the dates and for each date it will copy the value into Sheet Log in Column AF:

Sub foo()
Dim ws As Worksheet: Set ws = Sheets("alldata")
Dim wsLog As Worksheet: Set wsLog = Sheets("Log")
'declare and set your worksheet, amend as required
Dim LastRow As Long, LogLastRow As Long
LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
'get the last row with data on Column A
Dim c As Range, rng As Range

    ws.Rows("3:3").AutoFilter
    ws.Range("$A$3:$AA$" & LastRow).AutoFilter Field:=1, Operator:=xlFilterValues, Criteria2:=Array(0, "01/01/2018")
    Set rng = ws.Range("$A$4:$A$" & LastRow).SpecialCells(xlCellTypeVisible)

    For Each c In rng
        LogLastRow = wsLog.Cells(wsLog.Rows.Count, "AF").End(xlUp).Row
        c.Copy Destination:=wsLog.Cells(LogLastRow, "AF")
        'if instead of copying the value, you want to return its address,
        'you can get the address by using "c.Address" for each value in the range
    Next c
End Sub

How to write a macro to filter a column and take out the required value?

2 Answers2