| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shorter code
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/ |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shorter code
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 = = = = = = = = = = = = = = = = = "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 <snip |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Shorter way | Excel Discussion (Misc queries) | |||
| iso shorter equation | Excel Discussion (Misc queries) | |||
| Shorter Formula | Excel Discussion (Misc queries) | |||
| Shorter code | Excel Programming | |||
| shorter way? | Excel Programming | |||
![Stu[_13_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
