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 ?

 

[Domenic]

Unclear. The following macro assumes that the data has already been filtered, and that the second column of data is the target column. Change the specified column, accordingly.

<font face=Calibri><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> test()<br><br>    <SPAN style="color:#00007F">Dim</SPAN> rFilt <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> rCell <SPAN style="color:#00007F">As</SPAN> Range<br><br>    <SPAN style="color:#007F00">'Check whether the data has been filtered</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> ActiveSheet.FilterMode <SPAN style="color:#00007F">Then</SPAN><br>        MsgBox "Data not filtered!", vbExclamation<br>        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>    <SPAN style="color:#00007F">With</SPAN> ActiveSheet.AutoFilter.Range<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rFilt = .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> rFilt <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">With</SPAN> .Columns(2) <SPAN style="color:#007F00">'column 2 of data (change the column accordingly)</SPAN><br>                .Offset(1, 0).Resize(.Rows.Count - 1).FillDown<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">Else</SPAN><br>            MsgBox "No records found!", vbExclamation<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope this helps!

 

[Jalal Kasmani]

Thnx for the reply,not working - i m not getting any error but the codes are doing nothing

 

[Domenic]

Can you confirm which column you want to "dragdown"?

 

[Jalal Kasmani]

Hello Sir,

Thanx for the reply,

Sub zz()


Dim c, r As Long
Dim ra As String


c = Range("xfd1").End(xlToLeft).Column
r = Cells(65000, c).End(xlUp).Row


ra = Range("a1", Cells(r, c)).Address
ActiveSheet.Range(ra).AutoFilter field:=4, Criteria1:="10"




End Sub

Now if want to delete the data which contains 10(except headers). Columns and rows are dynamic - so i dont know how many rows and columns there will be.

 

[MARK858]

If you are just trying to clear the cells or delete the row then uncomment one of the commented rows below...

Sub zz()
    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:=4, Criteria1:="10"
        On Error Resume Next
        '.Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).ClearContents 'to clear teh cells
        
        '.Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete 'to delete entire row
        On Error GoTo 0
        .AutoFilter
    End With

 

[Jalal Kasmani]

Thanx for the reply,

What if the data starts from any row other then first row, so i want to filter and select 10 in field 4, and copy except headers (ie from second row of the data) and paste that in second row of the new sheet.

 

[MARK858]

How about stating exactly what you are trying to do and give screenshots of your data (see my signature for ways to this) as so far you have gone from autofill through deleting and now to copy data to another range.
It gets a bit irritating when you keep moving the goalposts.

 

[Jalal Kasmani]

Noted with thanks !

have just started with VBA and the excel dashboard.

 

[MARK858]

If you are just trying to generally learn about filters in vba have a look at the link below

http://www.contextures.com/xlautofilter03.html