Shorter code

[Stu]

I have a big macro code but it is mostly repeated is there anyway to
make this macro smaller?
below is the code:

Sub EuropeJan()

If Range("B3").Value = "" Then
Range("B4").EntireRow.Hidden = True
Else
Range("B4").EntireRow.Hidden = False
End If

If Range("B4").Value = "" Then
Range("B5").EntireRow.Hidden = True
Else
Range("B5").EntireRow.Hidden = False
End If

If Range("B5").Value = "" Then
Range("B6").EntireRow.Hidden = True
Else
Range("B6").EntireRow.Hidden = False
End If

If Range("B6").Value = "" Then
Range("B7").EntireRow.Hidden = True
Else
Range("B7").EntireRow.Hidden = False
End If

If Range("B7").Value = "" Then
Range("B8").EntireRow.Hidden = True
Else
Range("B8").EntireRow.Hidden = False
End If

If Range("B8").Value = "" Then
Range("B9").EntireRow.Hidden = True
Else
Range("B9").EntireRow.Hidden = False
End If

If Range("B9").Value = "" Then
Range("B10").EntireRow.Hidden = True
Else
Range("B10").EntireRow.Hidden = False
End If

If Range("B10").Value = "" Then
Range("B11").EntireRow.Hidden = True
Else
Range("B11").EntireRow.Hidden = False
End If

If Range("B11").Value = "" Then
Range("B12").EntireRow.Hidden = True
Else
Range("B12").EntireRow.Hidden = False
End If

If Range("B12").Value = "" Then
Range("B13").EntireRow.Hidden = True
Else
Range("B13").EntireRow.Hidden = False
End If

If Range("B13").Value = "" Then
Range("B14").EntireRow.Hidden = True
Else
Range("B14").EntireRow.Hidden = False
End If

If Range("B14").Value = "" Then
Range("B15").EntireRow.Hidden = True
Else
Range("B15").EntireRow.Hidden = False
End If

If Range("B15").Value = "" Then
Range("B16").EntireRow.Hidden = True
Else
Range("B16").EntireRow.Hidden = False
End If

If Range("B16").Value = "" Then
Range("B17").EntireRow.Hidden = True
Else
Range("B17").EntireRow.Hidden = False
End If

If Range("B17").Value = "" Then
Range("B18").EntireRow.Hidden = True
Else
Range("B18").EntireRow.Hidden = False
End If

If Range("B18").Value = "" Then
Range("B19").EntireRow.Hidden = True
Else
Range("B19").EntireRow.Hidden = False
End If

If Range("B19").Value = "" Then
Range("B20").EntireRow.Hidden = True
Else
Range("B20").EntireRow.Hidden = False
End If

If Range("B20").Value = "" Then
Range("B22").EntireRow.Hidden = True
Else
Range("B21").EntireRow.Hidden = False
End If

If Range("B21").Value = "" Then
Range("B21").EntireRow.Hidden = True
Else
Range("B21").EntireRow.Hidden = False
End If

End Sub


Sub EuropeFeb()

If Range("B28").Value = "" Then
Range("B29").EntireRow.Hidden = True
Else
Range("B29").EntireRow.Hidden = False
End If

If Range("B29").Value = "" Then
Range("B30").EntireRow.Hidden = True
Else
Range("B30").EntireRow.Hidden = False
End If

If Range("B30").Value = "" Then
Range("B31").EntireRow.Hidden = True
Else
Range("B31").EntireRow.Hidden = False
End If

If Range("B31").Value = "" Then
Range("B32").EntireRow.Hidden = True
Else
Range("B32").EntireRow.Hidden = False
End If

If Range("B32").Value = "" Then
Range("B33").EntireRow.Hidden = True
Else
Range("B33").EntireRow.Hidden = False
End If

If Range("B33").Value = "" Then
Range("B34").EntireRow.Hidden = True
Else
Range("B34").EntireRow.Hidden = False
End If

If Range("B34").Value = "" Then
Range("B35").EntireRow.Hidden = True
Else
Range("B35").EntireRow.Hidden = False
End If

If Range("B35").Value = "" Then
Range("B36").EntireRow.Hidden = True
Else
Range("B36").EntireRow.Hidden = False
End If

If Range("B36").Value = "" Then
Range("B37").EntireRow.Hidden = True
Else
Range("B37").EntireRow.Hidden = False
End If

If Range("B37").Value = "" Then
Range("B38").EntireRow.Hidden = True
Else
Range("B38").EntireRow.Hidden = False
End If

If Range("B38").Value = "" Then
Range("B39").EntireRow.Hidden = True
Else
Range("B39").EntireRow.Hidden = False
End If

If Range("B39").Value = "" Then
Range("B40").EntireRow.Hidden = True
Else
Range("B40").EntireRow.Hidden = False
End If

If Range("B40").Value = "" Then
Range("B41").EntireRow.Hidden = True
Else
Range("B41").EntireRow.Hidden = False
End If

If Range("B41").Value = "" Then
Range("B42").EntireRow.Hidden = True
Else
Range("B42").EntireRow.Hidden = False
End If

If Range("B42").Value = "" Then
Range("B43").EntireRow.Hidden = True
Else
Range("B43").EntireRow.Hidden = False
End If

If Range("B43").Value = "" Then
Range("B44").EntireRow.Hidden = True
Else
Range("B44").EntireRow.Hidden = False
End If

If Range("B44").Value = "" Then
Range("B45").EntireRow.Hidden = True
Else
Range("B45").EntireRow.Hidden = False
End If

If Range("B45").Value = "" Then
Range("B46").EntireRow.Hidden = True
Else
Range("B46").EntireRow.Hidden = False
End If

End Sub



Sub EuropeMar()

If Range("B52").Value = "" Then
Range("B53").EntireRow.Hidden = True
Else
Range("B53").EntireRow.Hidden = False
End If

If Range("B53").Value = "" Then
Range("B54").EntireRow.Hidden = True
Else
Range("B54").EntireRow.Hidden = False
End If

If Range("B54").Value = "" Then
Range("B55").EntireRow.Hidden = True
Else
Range("B55").EntireRow.Hidden = False
End If

If Range("B55").Value = "" Then
Range("B56").EntireRow.Hidden = True
Else
Range("B56").EntireRow.Hidden = False
End If

If Range("B56").Value = "" Then
Range("B57").EntireRow.Hidden = True
Else
Range("B57").EntireRow.Hidden = False
End If

If Range("B57").Value = "" Then
Range("B58").EntireRow.Hidden = True
Else
Range("B58").EntireRow.Hidden = False
End If

If Range("B58").Value = "" Then
Range("B59").EntireRow.Hidden = True
Else
Range("B59").EntireRow.Hidden = False
End If

If Range("B59").Value = "" Then
Range("B60").EntireRow.Hidden = True
Else
Range("B60").EntireRow.Hidden = False
End If

If Range("B60").Value = "" Then
Range("B61").EntireRow.Hidden = True
Else
Range("B61").EntireRow.Hidden = False
End If

If Range("B61").Value = "" Then
Range("B61").EntireRow.Hidden = True
Else
Range("B61").EntireRow.Hidden = False
End If

If Range("B61").Value = "" Then
Range("B62").EntireRow.Hidden = True
Else
Range("B62").EntireRow.Hidden = False
End If

If Range("B62").Value = "" Then
Range("B63").EntireRow.Hidden = True
Else
Range("B63").EntireRow.Hidden = False
End If

If Range("B63").Value = "" Then
Range("B64").EntireRow.Hidden = True
Else
Range("B64").EntireRow.Hidden = False
End If

If Range("B64").Value = "" Then
Range("B65").EntireRow.Hidden = True
Else
Range("B65").EntireRow.Hidden = False
End If

If Range("B65").Value = "" Then
Range("B66").EntireRow.Hidden = True
Else
Range("B66").EntireRow.Hidden = False
End If

If Range("B66").Value = "" Then
Range("B67").EntireRow.Hidden = True
Else
Range("B67").EntireRow.Hidden = False
End If

If Range("B67").Value = "" Then
Range("B68").EntireRow.Hidden = True
Else
Range("B68").EntireRow.Hidden = False
End If

If Range("B68").Value = "" Then
Range("B69").EntireRow.Hidden = True
Else
Range("B69").EntireRow.Hidden = False
End If

If Range("B69").Value = "" Then
Range("B70").EntireRow.Hidden = True
Else
Range("B70").EntireRow.Hidden = False
End If

End Sub



Sub EuropeApr()

If Range("B76").Value = "" Then
Range("B77").EntireRow.Hidden = True
Else
Range("B77").EntireRow.Hidden = False
End If

If Range("B77").Value = "" Then
Range("B78").EntireRow.Hidden = True
Else
Range("B78").EntireRow.Hidden = False
End If

If Range("B78").Value = "" Then
Range("B79").EntireRow.Hidden = True
Else
Range("B79").EntireRow.Hidden = False
End If

If Range("B79").Value = "" Then
Range("B80").EntireRow.Hidden = True
Else
Range("B80").EntireRow.Hidden = False
End If

If Range("B80").Value = "" Then
Range("B81").EntireRow.Hidden = True
Else
Range("B81").EntireRow.Hidden = False
End If

If Range("B81").Value = "" Then
Range("B82").EntireRow.Hidden = True
Else
Range("B82").EntireRow.Hidden = False
End If

If Range("B82").Value = "" Then
Range("B83").EntireRow.Hidden = True
Else
Range("B83").EntireRow.Hidden = False
End If

If Range("B83").Value = "" Then
Range("B84").EntireRow.Hidden = True
Else
Range("B84").EntireRow.Hidden = False
End If

If Range("B84").Value = "" Then
Range("B85").EntireRow.Hidden = True
Else
Range("B85").EntireRow.Hidden = False
End If

If Range("B85").Value = "" Then
Range("B86").EntireRow.Hidden = True
Else
Range("B86").EntireRow.Hidden = False
End If

 

[Stu]

And it carries on for another 2 posts if i were to post them.
Does anyone have any ideas?

 

[Chip Pearson]

Stu,

Try something like the following loop.

Dim Rng As Range
For Each Rng In Range("B3:B100") '<< adjust for correct range
Rng(2, 1).EntireRow.Hidden = (Rng.Value = "")
Next Rng


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com (e-mail address removed)

 

[Dana DeLouis]

Would this idea work for you?

Sub Demo()
[B3:B21].SpecialCells(xlBlanks).Offset(1, 0).EntireRow.Hidden = True
End Sub


The assumption here is that the rows are all unhidden at the start.
HTH
--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =

I have a big macro code but it is mostly repeated is there anyway to
make this macro smaller?
below is the code:

Sub EuropeJan()

If Range("B3").Value = "" Then
Range("B4").EntireRow.Hidden = True
Else
Range("B4").EntireRow.Hidden = False
End If

If Range("B4").Value = "" Then
Range("B5").EntireRow.Hidden = True
Else
Range("B5").EntireRow.Hidden = False
End If

<snip>

 

[Stu]

I need it to hide and unhide not just hide.

Thanks

Stu

 

[Stu]

What Chip has posted works,
I can't belive I spent hous on doing all of that code and you go and
give me a much shorter and easyer one after.
Thanks though

 

[Tom Ogilvy]

It looks like you have a pattern of 18 rows with the next month starting 7
rows after that. However, you code doesn't reflect that perfectly - and
there appears to be errors in your code such as the end of your EuropeJan

If Range("B20").Value = "" Then
Range("B22").EntireRow.Hidden = True
Else
Range("B21").EntireRow.Hidden = False
End If

If Range("B21").Value = "" Then
Range("B21").EntireRow.Hidden = True
Else
Range("B21").EntireRow.Hidden = False
End If

Where you pattern falls appart. Anyway, this routine will accept a Month
name or 3 letter month appreviation and hide the row after the row in column
b that has a blank.

Sub EuropeMonth(sMon As String)
Dim lngMonth As Long
Dim lngStart As Long, lngLen as Long
Dim rng As Range, rng1 As Range, cell As Range
Dim vRows As Variant
vRows = Array(3, 28, 51, 76, 100, 124, 148, 172, _
187, 212, 237, 262)
lngLen = 18
lngMonth = Month(DateValue(sMon & " 1, 2003"))
'lngStart = (lngMonth - 1) * (18 + 7) + 3
lngStart = vRows(lngMonth)
Set rng = Cells(lngStart + 1, 2).Resize(lngLen, 1)
rng.Offset(1, 0).EntireRow.Hidden = False
On Error Resume Next
Set rng1 = rng.SpecialCells(xlBlanks)
On Error GoTo 0
For Each cell In rng1
cell.Offset(1, 0).EntireRow.Hidden = True
Next
End Sub

Specify the correct starting row in the vRows array.

If the number of rows to be inspected isn't consistently 18, then this could
be handled with an array as well

vlen = Array(18, 19, . . . , 18)
lngLen = vlen(lngMonth)

 

[Stu]

I can't find the macro when I press alt + F8 it isn't in the macro list.
Is there any reason for this? And how do I start the macro running?

Thanks

Stu

 

[Tom Ogilvy]

Where did you put it? What did you name it?

Put it in a general/standard module (With your workbook as the default
project [ highlighted in project window in the VBE], do insert =>Module),
not a sheet module.

Public Sub MyMacro()
Dim Rng As Range
For Each Rng In Range("B3:B100") '<< adjust for correct range
Rng(2, 1).EntireRow.Hidden = (Rng.Value = "")
Next Rng
End Sub

 

[Stu]

i put it in a module,

do i still have to put?:

Public Sub MyMacro()
Dim Rng As Range
For Each Rng In Range("B3:B100") '<< adjust for correct range
Rng(2, 1).EntireRow.Hidden = (Rng.Value = "")
Next Rng
End Sub


Thanks Stu

 

[Tom Ogilvy]

But what did you put in the module?

 

[Stu]

Sub EuropeMonth(sMon As String)
Dim lngMonth As Long
Dim lngStart As Long, lngLen as Long
Dim rng As Range, rng1 As Range, cell As Range
Dim vRows As Variant
vRows = Array(3, 28, 51, 76, 100, 124, 148, 172, _
187, 212, 237, 262)
lngLen = 18
lngMonth = Month(DateValue(sMon & " 1, 2003"))
'lngStart = (lngMonth - 1) * (18 + 7) + 3
lngStart = vRows(lngMonth)
Set rng = Cells(lngStart + 1, 2).Resize(lngLen, 1)
rng.Offset(1, 0).EntireRow.Hidden = False
On Error Resume Next
Set rng1 = rng.SpecialCells(xlBlanks)
On Error GoTo 0
For Each cell In rng1
cell.Offset(1, 0).EntireRow.Hidden = True
Next
End Sub

 

[Tom Ogilvy]

You have to call that with another sub that tells it what month you want it
to do.

Sub CallJan()
EuropeMonth "Jan"
End Sub

Sub CallFeb()
EuropeMonth "Feb"
End Sub

But I thought you were using Chips code?

 

[Stu]

Im looking for the best code I can get and I want to find out more about
macros so I am questioning things to give me more knowledge.

::::::::::::::::
Stu