Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am experiencing issues with the following code not functioning properly. The code is not performing as expected. While stepping through the code (F8) I can see the oCell variable change for each cell in the range("GenAdmin"). I am able to step through each line of code and it will pass through to the IF statement but will not hide the row.

    If I reset the code and step through it again, it will work.

    [codebox]Sub HideRows()
    For Each oCell In Sheets("Sheet1").Range("GenAdmin")
    If Left(oCell, 5) = "Total" And oCell.Offset(-1, 1) = "" Then
    oTopRow = oCell.Offset(-2, 0).Row
    oBtmRow = oCell.Offset(0, 0).Row
    ActiveWorkbook.Sheets("Sheet1").Rows("" & oTopRow & ":" & oBtmRow & "").EntireRow.Hidden = True
    End If

    Next oCell
    End Sub
    [/codebox]

    Any ideas as to why?


    Thanks,
    John

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Change this
    Code:
    ActiveWorkbook.Sheets("Sheet1").Rows("" & oTopRow & ":" & oBtmRow & "").EntireRow.Hidden = True
    to

    Code:
    Sheets("Sheet2").Range(Cells(oTopRow, 1), Cells(oBtmRow, 1)).EntireRow.Hidden = True

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Mike,

    Thanks for the suggestion. I modified my code and it is still miss behaving. It does not hide the rows however as I previously stated if I Ctrl-Break the code and step through it, it runs just fine.

    Still puzzled...
    John




    [quote name='mbarron' post='774035' date='06-May-2009 18:56']Change this
    Code:
    ActiveWorkbook.Sheets("Sheet1").Rows("" & oTopRow & ":" & oBtmRow & "").EntireRow.Hidden = True
    to

    Code:
    Sheets("Sheet2").Range(Cells(oTopRow, 1), Cells(oBtmRow, 1)).EntireRow.Hidden = True
    [/quote]

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Still toying with this...

    It may be file corruption, now I cannot even run Range("K120").Select. The cell reference does not move to the appropriate cell ie K120.

    Regards,
    John


    [quote name='jstevens' post='774112' date='07-May-2009 07:37']Mike,

    Thanks for the suggestion. I modified my code and it is still miss behaving. It does not hide the rows however as I previously stated if I Ctrl-Break the code and step through it, it runs just fine.

    Still puzzled...
    John[/quote]

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If the sheet is not the active sheet, you won't be able to select a range on it. Whereabouts is that code located? In a normal module, worksheet module or ThisWorkbook module?
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Lounger
    Join Date
    Mar 2009
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Your code corrected so I could run it

    Code:
    Option Explicit
    
    Sub HideRows()
    	Dim oCell As Range, oTopRow As Long, oBtmRow As Long
    	For Each oCell In Sheets("Sheet1").Range("GenAdmin")
    		If Len(oCell) > 4 Then
    		If Left(oCell, 5) = "Total" And oCell.Offset(-1, 1) = "" Then
    			oTopRow = oCell.Offset(-2, 0).Row
    			oBtmRow = oCell.Offset(0, 0).Row
    			ActiveWorkbook.Sheets("Sheet1").Rows("" & oTopRow & ":" & oBtmRow & "").EntireRow.Hidden = True
    		End If
    		End If
    	Next oCell
    End Sub

  7. #7
    Lounger
    Join Date
    Mar 2009
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I though a find solution would be simple, but not as straightforward as I first thought. Should be quicker though, if you have a lot of data.
    Code:
    Sub HideRows2()
    	Dim c As Range, FirstAddress As String
    	With Sheets("Sheet1").Range("GenAdmin")
    		Set c = .Find("Total*", LookIn:=xlValues)
    		If Not c Is Nothing Then
    			FirstAddress = c.Address
    			Do
    				If c.Offset(-1, 1) = "" Then
    					c.Offset(-2).Resize(3).EntireRow.Hidden = True
    					Set c = .FindNext(c)
    					FirstAddress = c.Address
    					HideRows2
    				Else
    					Set c = .FindNext(c)
    				End If
    			Loop While Not c Is Nothing And c.Address <> FirstAddress
    		End If
    	End With
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •