Thread: Shorter code
View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Tom Ogilvy Tom Ogilvy is offline
external usenet poster
  
Posts: 27,285
Default Shorter code
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)

--
Regards,
Tom Ogilvy



Stu wrote in message
...
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



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/



Reply With QuoteReply With Quote