Hiding rows with a macro

[Aidan]

Hi there

Working my way through developing a spreadsheet for a charity that is easy for them to use and gives them graphical information for their current financial year.

(I had another thread entitled dont want to go back past a certain date but it didnt get any responses as probably didnt explain myself properly).

Anyway I have a sheet that shows the previous twelve months data and if that twelve months go back past Sep 16 then the values are shown as blank (October is the start of their financial year). I only then want to chart rows that have values and I tried this macro and it worked.....but when I changed the date it didnt bring back the hidden rows!!! (I got this macro from a website and changed the range to the range of my data) Any suggestions:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRg As Range
Application.ScreenUpdating = False
For Each xRg In Range("c40:c51")
If xRg.Value = "" Then
xRg.EntireRow.Hidden = True

Else
xRg.EntireRow.Hidden = False
End If
Next xRg
Application.ScreenUpdating = True
End Sub

 

[Aidan]

Just place that VBA code in any module in your workbook, and then run it.
That will make sure that your events are enabled.
Then try again.

Hi Joe

Thanks. That works. Can I ask one last question.

I have a range of date that is dependent on that date that then creates a series of graphs. So for example c5:c16 i would like to run the same macro - is there a way of adding it in as a line underneath?

Cheers


Aidan

 

[Joe4]

You can handle multiple ranges simply by amending your FOR statement to something like this:

For Each xRg In ws.Range("C5:C16, C40:C51")

Is that what you are looking for?

 

[Aidan]

yes, thank you, you are a star

 

[marlline]

Hi guys,

I know there are many posts on this topic. However I still could not figure out for my specific case. Please help. Thank you!
It errors at
Selection.AutoFill Destination:=ThisWorkbook.Sheets("Output").Range("A" & targetrow & ":C" & LastRow), Type:=xlFillCopy

The full code is as follows:
Sub DoFolder(Folder)
Dim SubFolder
Dim WKB_SOURCE As Workbook
Dim LastRow As Integer
Dim targetrow As Integer




For Each SubFolder In Folder.SubFolders
DoFolder SubFolder
Next
Dim File
For Each File In Folder.Files
targetrow = ThisWorkbook.Sheets("Output").Range("D20000").End(xlUp).Row + 1
Set WKB_SOURCE = Workbooks.Open(File, False)
WKB_SOURCE.Sheets(1).Unprotect ("BD MAP")
LastRow = WKB_SOURCE.Sheets(1).Range("C2000").End(xlUp).Row

WKB_SOURCE.Sheets(1).Range("C10:K" & LastRow).Copy
ThisWorkbook.Sheets("Output").Activate
ThisWorkbook.Sheets("Output").Range("D" & targetrow).PasteSpecial xlPasteValues
ThisWorkbook.Sheets("Output").Range("D" & targetrow).PasteSpecial xlPasteFormats

WKB_SOURCE.Sheets(1).Range("D2:D4").Copy
ThisWorkbook.Sheets("Output").Activate
ThisWorkbook.Sheets("Output").Range("A" & targetrow).PasteSpecial xlPasteValues, Transpose:=True
LastRow = ThisWorkbook.Sheets("Output").Range("D20000").End(xlUp).Row
ThisWorkbook.Sheets("output").Activate
ThisWorkbook.Sheets("Output").Range("A" & targetrow & ":C" & targetrow).Select
Selection.AutoFill Destination:=ThisWorkbook.Sheets("Output").Range("A" & targetrow & ":C" & LastRow), Type:=xlFillCopy





WKB_SOURCE.Sheets(1).Protect ("BD MAP")
WKB_SOURCE.Close savechages

Next
LastRow = ThisWorkbook.Sheets("Output").Range("D50000").End(xlUp).Row
ThisWorkbook.Sheets("Output").Range("D2").Copy
ThisWorkbook.Sheets("Output").Range("A2:C" & LastRow).PasteSpecial xlPasteFormats

End Sub