| 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/ |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shorter code
And it carries on for another 2 posts if i were to post them.
Does anyone have any ideas? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
|
#4
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shorter code
I need it to hide and unhide not just hide.
Thanks Stu ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shorter code
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 -- Stu ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
|
#7
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/ |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shorter code
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 ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shorter code
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 -- Regards, Tom Ogilvy Stu wrote in message ... 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 ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shorter code
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 ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shorter code
But what did you put in the module?
-- Regards, Tom Ogilvy Stu wrote in message ... 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 ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shorter code
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 ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shorter code
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? -- Regards, Tom Ogilyv "Stu" wrote in message ... 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 ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shorter code
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 ------------------------------------------------ ~~ 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)
Linear Mode
