If Not (.Rows.Hidden) problem

Yamezz

Active Member
Joined
Nov 22, 2006
Messages
372
Office Version
  1. 365
I have code that filters for a date range, then needs to run a copy/paste if there is visible data after filtering.
Rich (BB code):
ActiveSheet.Range("A3:Y1000000").AutoFilter field:=8, Criteria1:=">=" & Format(StartDate, "mm/dd/yyyy"), Operator:=xlAnd, Criteria2:="<=" & Format(EndDate, "mm/dd/yyyy")

With ActiveSheet.Range("H2").Offset(1, 0)
    If Not (.Rows.Hidden) Then

        Range("H3:H" & Cells(Rows.Count, "H").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
        Sheets("BR").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteAll
        
        Range("B3:B" & Cells(Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
        Sheets("BR").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteAll
        
        Range("I3:I" & Cells(Rows.Count, "I").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
        Sheets("BR").Range("D" & Rows.Count).End(xlUp).Offset(1, -1).PasteSpecial Paste:=xlPasteAll
    End If
End With

The trouble is, my code in bold is bypassing the copy/paste, even if there's filtered results to work with.
At the moment I have headers in rows 1 and 2 and the filtered results show rows 37, 44 and 50. I presume my current code is checking row 3, finding it hidden, so bypassing the copy paste, whereas I'd like it to check the next visible row under row 2. Should I be using some kind of .Rows.Hidden and xlCellTypeVisible combination to make the copy/paste occur when there's filtered results?

Thanks.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Hi,
not tested but see if this change to your code works does what you want.

Code:
Dim lr As Long, FilterRange As Long
    Dim rng As Range


    With ActiveSheet
        .Range("A1").AutoFilter
        lr = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("A2:Y" & lr).AutoFilter field:=8, _
                                       Criteria1:=">=" & Format(StartDate, "mm/dd/yyyy"), _
                                       Operator:=xlAnd, _
                                       Criteria2:="<=" & Format(EndDate, "mm/dd/yyyy")


        Set rng = .AutoFilter.Range
        'check there are some visible rows to copy
        FilterRange = rng.Columns(8).SpecialCells(xlCellTypeVisible).Count - 2


        If FilterRange > 0 Then


            'exclude header row
            Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)


            .Range("H3:H" & Cells(Rows.Count, "H").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
            Sheets("BR").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteAll


            .Range("B3:B" & Cells(Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
            Sheets("BR").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteAll


            .Range("I3:I" & Cells(Rows.Count, "I").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
            Sheets("BR").Range("D" & Rows.Count).End(xlUp).Offset(1, -1).PasteSpecial Paste:=xlPasteAll
        End If
    End With
    rng.AutoFilter

Dave
 
Upvote 0
Thanks Dave. I just replaced
Code:
With ActiveSheet.Range("H2").Offset(1, 0)
    If Not (.Rows.Hidden) Then
with
Code:
With ActiveSheet
    If .AutoFilter.Range.Columns(8).SpecialCells(xlCellTypeVisible).Count - 2 > 0 Then

Do you see any potential problems later on with this simple change or should I be using your longer code?
 
Upvote 0

Forum statistics

Threads
1,226,498
Messages
6,191,376
Members
453,655
Latest member
lasvegasbuffet

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