VBA - Combining three VBA modules

[dnegrotto]

I have two fold question ...one I want to add to this Module (I am a total newbie at doing this) but basically I wanted to have a macro that does all three of these in one shot (macro).... as of right now they are three separate macros to do the work.


Explanation of Macro - basically each macro finds a specific row and column to look for the first blank cell and than hides all blank cells....It does this for three different places in my work sheet (3 different graphs that have dynamic data)


I also wanted to add two more "macro's" so to speak that does the same thing except it doesn't hide the first blank row...so basically hides the second blank row found and onwards. Please let me know if:

1) I can combine these Macro's into ONE Macro

2) How I can re-arrange the code so that it wont hide the first blank row, but will hide the second and than after:


Sub HURows()

BeginRow = 12

EndRow = 152

ChkCol = 37


For RowCnt = BeginRow To EndRow

If Cells(RowCnt, ChkCol).Value = "" Then

Cells(RowCnt, ChkCol).EntireRow.Hidden = True

Else

Cells(RowCnt, ChkCol).EntireRow.Hidden = False

End If

Next RowCnt

End Sub


Sub Chart2()

BeginRow = 165

EndRow = 298

ChkCol = 44


For RowCnt = BeginRow To EndRow

If Cells(RowCnt, ChkCol).Value = "" Then

Cells(RowCnt, ChkCol).EntireRow.Hidden = True

Else

Cells(RowCnt, ChkCol).EntireRow.Hidden = False

End If

Next RowCnt

End Sub

Sub Chart3()

BeginRow = 312

EndRow = 464

ChkCol = 88


For RowCnt = BeginRow To EndRow

If Cells(RowCnt, ChkCol).Value = "" Then

Cells(RowCnt, ChkCol).EntireRow.Hidden = True

Else

Cells(RowCnt, ChkCol).EntireRow.Hidden = False

End If

Next RowCnt

End Sub

 

[Hui]

You can combine them into 2 macros to provide a bit more room for expansion and modularistaion


HideRows is the main module and it calls the other module HR 3 times

[pre]

Sub HideRows()
BeginRow = 12
EndRow = 152
ChkCol = 37

Call HR(BeginRow, EndRow, ChkCol)

BeginRow = 165
EndRow = 298
ChkCol = 44

Call HR(BeginRow, EndRow, ChkCol)

BeginRow = 312
EndRow = 464
ChkCol = 88

Call HR(BeginRow, EndRow, ChkCol)

End Sub

This is the module that does the work


Sub HR(BeginRow, EndRow, ChkCol)

For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = "" Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt

End Sub

[/pre]
The above 2 modules replace all your 3 modules

You should be able to follow it clearly


Try it on a copy of your file first

 

[xld]

HR can be greatly simplified

[pre]
Sub HR(ByVal BeginRow As Long, ByVal EndRow As Long, ByVal ChkCol As Variant)

For RowCnt = BeginRow To EndRow

Rows(RowCnt).Hidden = Cells(RowCnt, ChkCol).Value = ""
Next RowCnt
End Sub
[/pre]

 

[dnegrotto]

thanks for the responses - did any of these modules make it so that one blank row will not be hidden, for the last one I want it to hide all the blank rows except for the first instance

 

[dnegrotto]

thanks for the responses - did any of these modules make it so that one blank row will not be hidden, for the last one I want it to hide all the blank rows except for the first instance

 

[Luke M]

Looks like Hui and xld missed that part. Adding on to Hui's code, we can add another variable to state how many blank rows you want to keep:

Sub HideRows()

BeginRow = 12

EndRow = 152

ChkCol = 37

KeepBlanks = 0


  Call HR(BeginRow, EndRow, ChkCol, KeepBlanks)


  BeginRow = 165

EndRow = 298

ChkCol = 44

KeepBlanks = 1


  Call HR(BeginRow, EndRow, ChkCol, KeepBlanks)


  BeginRow = 312

EndRow = 464

ChkCol = 88

KeepBlanks = 1


  Call HR(BeginRow, EndRow, ChkCol, KeepBlanks)


End Sub


Sub HR(BeginRow, EndRow, ChkCol, KeepBlanks)

BlankCount = 0

For RowCnt = BeginRow To EndRow

If Cells(RowCnt, ChkCol).Value = "" Then

BlankCount = BlankCount + 1

If BlankCount > KeepBlanks Then

Cells(RowCnt, ChkCol).EntireRow.Hidden = True

End If

Else

Cells(RowCnt, ChkCol).EntireRow.Hidden = False

End If

Next RowCnt


End Sub