Macro to copy filtered data, not including the top row

akg742

New Member
Joined
Mar 13, 2014
Messages
39
I have a table, with headers, that has set filters. Once the filters are what I need them to be (outside of the macro), I want to copy the data showing to another tab. Without the filters, I'd want columns A through G, rows 2 through the last with content but, with the filters, I can't predict what the top row will be. What language do I need to use to effectively select A1 and then arrow down one row to define the top cell?

Thanks!
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Hi,
If you need the row number of the first visible row after filtering
VBA Code:
x = ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 2).Row
 
Upvote 0
Hi,
If you need the row number of the first visible row after filtering
VBA Code:
x = ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 2).Row
Thanks! How would I use x to define my range? If I were starting at A2, I'd use Range("A2:G" & Lastrow).Select but I have no idea how to rephrase that.
 
Upvote 0
Do you actually need to know the 1st visible row or are you just trying to copy the filtered data?
 
Upvote 0
Do you actually need to know the 1st visible row or are you just trying to copy the filtered data?
I just need to copy the filtered data in the rows I'm interested in (A-G, I don't care about what's in H and I). I'm used to setting ranges so assumed I'd need to define the first and last rows but, if it's easier to do what I want without that step, I'm all for it.
 
Upvote 0
If you were filtering on column F for "Completed" as an example

VBA Code:
Sub Filterit()
    Application.ScreenUpdating = False
   
    With Sheets(1).Range("A1:G" & Sheets(1).Range("A" & Rows.Count).End(xlUp).Row)
   
        .AutoFilter 6, "Completed"
       
        On Error Resume Next
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).Copy Sheets(2).Range("A" & Rows.Count).End(xlUp).Offset(1)
        On Error GoTo 0


        Sheets(1).ShowAllData
   
    End With
   
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
If you were filtering on column F for "Completed" as an example

VBA Code:
Sub Filterit()
    Application.ScreenUpdating = False
  
    With Sheets(1).Range("A1:G" & Sheets(1).Range("A" & Rows.Count).End(xlUp).Row)
  
        .AutoFilter 6, "Completed"
      
        On Error Resume Next
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).Copy Sheets(2).Range("A" & Rows.Count).End(xlUp).Offset(1)
        On Error GoTo 0


        Sheets(1).ShowAllData
  
    End With
  
    Application.ScreenUpdating = True
End Sub
I don't actually need the filtering in the macro. The settings there will change depending on a few factors. The macro would be run after the user organized the data to show what is needed at that point. I tried your code without the filter lines but I don't think I translated everything into my terms because it copied a random cell in column E from the source to E1 in the destination. The filtered contents of A-G in Report is my source and the destination would start at the first empty cell in row E of Risk Assessments (I know that part's not right. I copied it from another macro and need to add the offset).

VBA Code:
    With Sheets("Report").Range("A1:G" & Sheets("Report").Range("A" & Rows.Count).End(xlUp).Row)
        
        On Error Resume Next
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).Copy Sheets(2).Range("A" & Rows.Count).End(xlUp).Offset(1)
        On Error GoTo 0
   
    End With
    
    Selection.Copy
    Sheets("Risk Assessments").Select
    Range("E2").Select
    Selection.End(xlUp).Select
    ActiveSheet.paste
 
Upvote 0
VBA Code:
    Range("E2").Select
    Selection.End(xlUp).Select
What are you trying to do here? The only cell that could be s E1
 
Upvote 0
If you mean the first empty cell in column E then you want

VBA Code:
Sub Filterit()
    Application.ScreenUpdating = False
    
    With Sheets("Report").Range("A1:G" & Sheets("Report").Range("A" & Rows.Count).End(xlUp).Row)
      
        On Error Resume Next
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).Copy Sheets("Risk Assessments").Range("E" & Rows.Count).End(xlUp).Offset(1)
        On Error GoTo 0

     End With
    
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Solution
If you mean the first empty cell in column E then you want

VBA Code:
Sub Filterit()
    Application.ScreenUpdating = False
   
    With Sheets("Report").Range("A1:G" & Sheets("Report").Range("A" & Rows.Count).End(xlUp).Row)
     
        On Error Resume Next
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).Copy Sheets("Risk Assessments").Range("E" & Rows.Count).End(xlUp).Offset(1)
        On Error GoTo 0

     End With
   
    Application.ScreenUpdating = True
End Sub

Sorry for the delay but this worked perfectly. Thank you so much!
 
Upvote 0

Forum statistics

Threads
1,213,480
Messages
6,113,899
Members
448,530
Latest member
yatong2008

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top