Selecting second row after doing the filter

[Jalal Kasmani]

After filter, i want the cursor on the second row, then select that row and dragdown. can anyone help ?

 

[Jalal Kasmani]

Thanks so much Sir!

 

[Jalal Kasmani]

Can i kindly get the links of all other codes like pivot, range, loops etc Thanks!

 

[MARK858]

I am afraid what you are asking is much too vague, for instance there are whole books written about pivot tables.
The list in the link below by Hiker95 (post.#2) might get you started on the basics.
It is probably not his current list but on my phone and so searching is a bit more difficult.


http://www.mrexcel.com/forum/genera...good-teaching-resource-excel.html#post3442929

 

[Jalal Kasmani]

hi,

the code which you provided does not work if the data starts from fourth row.

Sub filter()


Dim c As Long, r As Long


c = Cells(1, Columns.Count).End(xlToLeft).Column
r = Cells(Rows.Count, c).End(xlUp).Row

With ActiveSheet.Range("a1", Cells(r, c))
.AutoFilter field:=5, Criteria1:="10"
On Error Resume Next
'.Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).ClearContents 'to clear the cells

'.Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete 'to delete entire row
On Error GoTo 0
.AutoFilter
End With

i want to dynamically filter the data bcoz sometimes my data starts from row second and sometimes from 3rd or 4th .

 

[MARK858]

 With ActiveSheet.Range("a[COLOR="#FF0000"][B]4[/B][/COLOR]", Cells(r, c))

 

[Jalal Kasmani]

And i need to change the below code as well

c = Cells(4, Columns.Count).End(xlToLeft).Column

But the user wont be able to change the codes every month. i want whenever i open the file ,,, from whichever row the data is starting i want to filter that and do my workings accordingly.

 

[MARK858]

Sub Macro2()
    '
    ' Macro2 Macro
    '

    Dim x As Long, c As Long, r As Long

    With ActiveSheet
        c = .Cells.Find(What:="*", SearchOrder:=xlColumns, SearchDirection:=xlPrevious, LookIn:=xlValues).Column
        r = .Cells(Rows.Count, c).End(xlUp).Row
        x = .Range("A1").End(xlDown).Row
        With .Range("a" & x, Cells(r, c))
            .AutoFilter field:=5, Criteria1:="10"
            On Error Resume Next
            '.Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).ClearContents 'to clear the cells

            '.Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete 'to delete entire row
            On Error GoTo 0
            .AutoFilter


        End With
    End With

End Sub

 

[Jalal Kasmani]

thnx for the reply,

this is not working if the data starts from the first row.

 

[MARK858]

Try

Sub Macro2()

    Dim x As Long, c As Long, r As Long

    With ActiveSheet
        c = .Cells.Find(What:="*", SearchOrder:=xlColumns, SearchDirection:=xlPrevious, LookIn:=xlValues).Column
        r = .Cells(Rows.Count, c).End(xlUp).Row

        If .Cells(1, 1) <> "" Then
            x = 1
        Else
            x = .Range("A1").End(xlDown).Row
        End If

        With .Range("a" & x, Cells(r, c))
            .AutoFilter field:=5, Criteria1:="10"
            On Error Resume Next
            '.Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).ClearContents 'to clear the cells

            '.Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete 'to delete entire row
            On Error GoTo 0
            .AutoFilter


        End With
    End With

End Sub

 

[Jalal Kasmani]

Thnx Mark for the reply,

if i want to learn very details about find from basics to advanced. which online book or sites will i get ?