If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Hide Column If Usedrange of Column ISBLANK Follow-up
Hello all,
I'm following up to my first post. I'm looking to loop through columns looking at a range that varies in rows. I'm looking to hide the columns with the range of rows that are blank or empty or xlspecialcells. The range will be the active column, beginning with row 12 to the last used cell in the column. This code works for cycling through the columns but, it only looks at one row. In this code it's looking at row 2. If .Cells(2, iCol). I need it to look at cells from row 12 to the last used row and hide columns that are blank in that range. Any assistance really appreciated. As always thank you in advance, Ron Option Explicit Private Sub ToggleButton1_Click() Dim iCol As Long Dim iLastCol As Long With Worksheets("2006 Realized Gains") iLastCol = Cells.SpecialCells(xlCellTypeLastCell).Column For iCol = 1 To iLastCol If .Cells(2, iCol).Font.ColorIndex = 5 _ And .Cells(2, iCol).Interior.ColorIndex = 24 Then .Columns(iCol).Hidden = ToggleButton1.Value Else .Columns(iCol).Hidden = False End If Next iCol 'this is just for looks when I'm done Range("A1").Select End With End Sub |
#2
|
|||
|
|||
Hide Column If Usedrange of Column ISBLANK Follow-up
Did you try Dave Peterson's solution in your previous post?
It does what you want to do... -- Pl click the ''''Yes'''' button (if you see it - don''''t worry if you don''''t), if this answer was helpful. " wrote: Hello all, I'm following up to my first post. I'm looking to loop through columns looking at a range that varies in rows. I'm looking to hide the columns with the range of rows that are blank or empty or xlspecialcells. The range will be the active column, beginning with row 12 to the last used cell in the column. This code works for cycling through the columns but, it only looks at one row. In this code it's looking at row 2. If .Cells(2, iCol). I need it to look at cells from row 12 to the last used row and hide columns that are blank in that range. Any assistance really appreciated. As always thank you in advance, Ron Option Explicit Private Sub ToggleButton1_Click() Dim iCol As Long Dim iLastCol As Long With Worksheets("2006 Realized Gains") iLastCol = Cells.SpecialCells(xlCellTypeLastCell).Column For iCol = 1 To iLastCol If .Cells(2, iCol).Font.ColorIndex = 5 _ And .Cells(2, iCol).Interior.ColorIndex = 24 Then .Columns(iCol).Hidden = ToggleButton1.Value Else .Columns(iCol).Hidden = False End If Next iCol 'this is just for looks when I'm done Range("A1").Select End With End Sub |
Thread Tools | |
Display Modes | |
|
|