Excel VBA / Macro Assistance Required - Hidden Rows

Question

I'm sure that this is a relatively simple query, but to say I'm an amateur with VBA would be a compliment.

What I'm trying to do is use a macro button to copy certain information from one sheet to a mastersheet. This is working fine, until I hide the rows (there are 880 rows in total and given that these sit alongside data entry tables, I kinda need to hide them to ease navigation).

This is the code that I am presently using - could it be amended so as to include hidden rows?

Thank you in advance,

Rob

Private Sub CopyDataTeam1()
    Application.ScreenUpdating = False
    Dim copySheet As Worksheet
    Dim pasteSheet As Worksheet

    Set copySheet = ActiveSheet
    Set pasteSheet = Worksheets("MainData")

    copySheet.Range("AY5:BC5", copySheet.Range("AY5:BC5").End(xlDown)).Copy
    pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

    Sheets("MainData").Cells.Replace What:="-", Replacement:="", LookAt:=xlWhole, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

"This is working fine, until I hide the rows" Uh, OK, Then what happens? Tell us what the problem is, including any error messages. — RBarryYoung
Thank you for your comment and apologies for being so vague. Basically, there isn't an error, but I'm expecting 875 rows of data to be copied, but instead, it only copies over the 29 rows which are not hidden. — HendrixAndChill

Sam Sam · Accepted Answer · 2013-11-16T15:39:25

One solution could be to check your sheet first for hidden rows. Store that to a Range object.

Unhide your range, do your stuff, then rehide your range...

'pass in a worksheet, and get all the hidden rows
Function HiddenRange(ws As Worksheet) As Range

Dim hideRange As Range
Dim column As Long

    'use column a
    column = 1

'if your hidden rows are at the end of your sheet, then 
'.End(xlUp) may not capture the end of the sheet correctly.
'could use UsedRange.Rows instead..
For i = 1 To ws.UsedRange.Rows.Count 'ws.Cells(ws.Rows.Count, column).End(xlUp).Row

    If ws.Rows(i).Hidden Then

        If hideRange Is Nothing Then

        Set hideRange = ws.Rows(i)

        Else

        Set hideRange = Application.Union(ws.Rows(i), hideRange)

        End If

    End If

Next i

'return our hidden range
    If hideRange Is Nothing = False Then

    Set HiddenRange = hideRange

    End If

End Function


Public Sub UsageExample()

Dim rng As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'disable error tracking as we get type mismatch if rng is set to nothing
On Error Resume Next
Set rng = HiddenRange(Sheet1)

'resume error handling
On Error GoTo err


If Not rng Is Nothing Then rng.Rows.Hidden = False


'do your stuff in here

If Not rng Is Nothing Then rng.Rows.Hidden = True


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Exit Sub

err:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

MsgBox err.Description, vbExclamation, "An error occured"

End Sub

You may also want to change the way you set your copy range. Don't use xlDown, as if you have any blank cells in Column BC the range won't be set properly.

Changing it to the below will set the range based on the bottom value in column BC

copySheet.Range(copySheet.Range("AY5"), copySheet.Range("BC" & copySheet.Rows.Count).End(xlUp)).Copy

Excel VBA / Macro Assistance Required - Hidden Rows

1 Answers