VBA to detect hidden rows. then Re-hide

[ItalianPlatinum]

I have a line of code that transfers data over to a tab based off of data in cell A2. When doing so it un-hides my source data, and doesn't re-hide that source data after. I am looking for a way to detect what is hidden before the code runs, run the code, then reapply the hidden rows. Currently I have the below BUT the hidden rows aren't always that range so I am looking for a dynamic approach. Any help is much appreciated

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Set ws = Sheets("Sheet1")  '<--- change data to actual sheet name that holds the source data
    If Target.Address(0, 0) <> "A2" Then Exit Sub
    Application.EnableEvents = False
    [a4].CurrentRegion.Offset(1).Clear
    If (Not Target Like "*[!0-9]*") * (Len(Target) = 4) Then
        With ws.Range("a1", ws.Cells.SpecialCells(11))
            .AutoFilter 18, , 7, Array(0, "1/25/" & Target)
            .Copy [a3]
            .AutoFilter
        End With
        'ws.Rows("2:5030").Hidden = True
    End If
    Application.EnableEvents = True
End Sub

 

[Fuji]

This is confusing.
Your data starts from A4 and header in row 3 in your other thread.

How is/are your sheet(s) structured and how exactly you want to happen?

 

[ItalianPlatinum]

Yes what I provided in the prior was a sample because the data was so large. The real data has row 1 as header and row 2 with data starting. With 33 columns of data. Does that pose a problem?

The user has rows hidden and needs them to remain hidden after the VBA is run. Now sure how we can get to that. So that is where I need some help.

I tried doing a set range where we can modify as we go but apparently the range changes frequently so I cannot force it and I don't want the user to touch the VBA so I talked myself out of that approach.

'ws.Rows("2:5030").Hidden = True

.

 

[Fuji]

Sheet1 holds the data with header in 1st row and data starts from row2.
Output to the other sheet from A4, header in row4, and you are entering year in A2.
Output must the result from Sheet1(when filtered, it should keep only filtered data) and you want to keep Sheet1 data as it is.
To output sheet code module.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    If Target.Address(0, 0) <> "A2" Then Exit Sub
    If Not Target Like "####" Then Exit Sub
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    [a4].CurrentRegion.Clear: Set r = [r1:r2]
    Sheets("sheet1").[a1].CurrentRegion.Copy [a4]
    r(2).Formula = "=year(b5)<>$a$2"
    [a4].CurrentRegion.AdvancedFilter 1, r: r.Clear
    [a4].CurrentRegion.Offset(1).EntireRow.Delete
    If Me.FilterMode Then Me.ShowAllData
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

 

[ItalianPlatinum]

On my summary sheet - If I wanted the year to be in A1 and for the header to be on A3 with data on A4 would it be something like this?

Sheet1. Data starts row 2, with header on row 1. Shipment date is on Column R

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    If Target.Address(0, 0) <> "A1" Then Exit Sub
    If Not Target Like "####" Then Exit Sub
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    [a3].CurrentRegion.Clear: Set r = [r1:r2]
    Sheets("sheet1").[a1].CurrentRegion.Copy [a3]
    r(2).Formula = "=year(r5)<>$A$1"
    [a3].CurrentRegion.AdvancedFilter 1, r: r.Clear
    [a3].CurrentRegion.Offset(1).EntireRow.Delete
    If Me.FilterMode Then Me.ShowAllData
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

 

[Skyybot]

ws.Range(Rows(1), Rows(5030)).Hidden = True

 

[ItalianPlatinum]

ws.Range(Rows(1), Rows(5030)).Hidden = True

This is under the assumption the rows hidden would always be 1-5030?

 

[Skyybot]

This is under the assumption the rows hidden would always be 1-5030?

Yes. Though you can make that Range anything that you want.