Unhide Columns in certain range

**Desert Piranha** · 07-12-2006, 03:23 AM

Hi all,

I have columns D through BD where data is pasted from another workbook, one column per week (via code).

So unused columns on the right are hidden. They are not completly unused as there are rows with formulas..

Based on row 3 I am trying to:
When the data (for this week as example) is pasted in then that coulmn will be unhidden.

I am trying to change the 'End(xlToLeft)" to work with 'Columns D through BD' only.
Cells(3, Columns.Count).End(xlToLeft).Offset(0, 1).EntireColumn.Hidden = False

I can't use that, because columns to the right, BE through BF have data and that is blocking.

Any direction is surely appreciated.

crazybass2 · 07-12-2006, 10:35 AM

Place this code in the module for the sheet in question.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Not Intersect(Target, Range("D3:BD3")) Is Nothing Then
For Each rng In Range("D3:BD3")
If rng <> "" Then Columns(rng.Column).Hidden = False
Next rng
End If
End Sub

Mike

"Desert Piranha" wrote:

>
> Hi all,
>
> I have columns D through BD where data is pasted from another workbook,
> one column per week (via code).
>
> So unused columns on the right are hidden. They are not completly
> unused as there are rows with formulas..
>
> Based on row 3 I am trying to:
> When the data (for this week as example) is pasted in then that coulmn
> will be unhidden.
>
> I am trying to change the 'End(xlToLeft)" to work with 'Columns D
> through BD' only.
> Cells(3, Columns.Count).End(xlToLeft).Offset(0, 1).EntireColumn.Hidden
> = False
>
> I can't use that, because columns to the right, BE through BF have data
> and that is blocking.
>
> Any direction is surely appreciated.
>
>
> --
> Desert Piranha
>
>
> ------------------------------------------------------------------------
> Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
> View this thread: http://www.excelforum.com/showthread...hreadid=560574
>
>

**Desert Piranha** · 07-12-2006, 08:21 PM

Originally Posted by crazybass2

Place this code in the module for the sheet in question.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Not Intersect(Target, Range("D3:BD3")) Is Nothing Then
For Each rng In Range("D3:BD3")
If rng <> "" Then Columns(rng.Column).Hidden = False
Next rng
End If
End Sub

Mike

Hi crazybass2,

Your code is great as a stand alone but i am having trouble putting it in this existing code,

Sub aTest()
    
    'Unhide last used Column Based on row 3
    '''Tried several variations, no luck
    '''Tried changing the name and puting it outside this code with a call here, no luck
    '''    Dim rng As Range
    '''    If Not Intersect(Target, Range("D3:BD3")) Is Nothing Then
    '''    For Each rng In Range("D3:BD3")
    '''    If rng <> "" Then Columns(rng.Column).Hidden = False
    '''    Next rng
    
    'Everything below here works ok
    
    'Color cells with a zero to red
    Dim rCell As Range
    For Each rCell In Range("TheRange")
        'Code to run on each cell
        If rCell.Value = "0" Then
            rCell.Interior.ColorIndex = 3
        Else
            rCell.Interior.ColorIndex = xlAutomatic
        End If
    Next rCell
    
    'Format yellow (total) rows
    Range("D14:BL14,D26:BL26,D37:BL37,D47:BL47,D57:BL57,D68:BL68,D79:BL79").Select
    Selection.Interior.ColorIndex = 6
    Selection.HorizontalAlignment = xlRight
    Selection.VerticalAlignment = xlCenter
    Selection.NumberFormat = "#,##0.00_);(#,##0.00)"
    
    'Move window back to the top
    ActiveWindow.SmallScroll Down:=-67
    
    'and select cell
    Range("A1").Select
End Sub

crazybass2 · 07-13-2006, 11:35 AM

Desert,

From you initial post I thought you wanted this action to occur when data
was pasted into the cells. It appears now that you what this action to occur
only when "aTest()" is run. The code is basically the same, but you need to
remove the intersect statement.

Assuming that "aTest()" is in the Sheet module for the sheet in question....

Sub aTest()
Dim rng As Range
For Each rng in Range("D3:BD3")
If rng <> "" Then Columns(rng.Column).Hidden = False
Next rng
'The rest of your code goes here
End Sub

That should do it. If "aTest()" is in another module some modification will
need to be done, let me know where it is and what sheet you are modifying if
this is the case

Mike

"Desert Piranha" wrote:

>
> crazybass2 Wrote:
> > Place this code in the module for the sheet in question.
> >
> > Option Explicit
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Dim rng As Range
> > If Not Intersect(Target, Range("D3:BD3")) Is Nothing Then
> > For Each rng In Range("D3:BD3")
> > If rng <> "" Then Columns(rng.Column).Hidden = False
> > Next rng
> > End If
> > End Sub
> >
> >
> > MikeHi crazybass2,
>
> Your code is great as a stand alone but i am having trouble putting it
> in this existing code,
>
> Code:
> --------------------
> Sub aTest()
>
> 'Unhide last used Column Based on row 3
> '''Tried several variations, no luck
> '''Tried changing the name and puting it outside this code with a call here, no luck
> ''' Dim rng As Range
> ''' If Not Intersect(Target, Range("D3:BD3")) Is Nothing Then
> ''' For Each rng In Range("D3:BD3")
> ''' If rng <> "" Then Columns(rng.Column).Hidden = False
> ''' Next rng
>
> 'Everything below here works ok
>
> 'Color cells with a zero to red
> Dim rCell As Range
> For Each rCell In Range("TheRange")
> 'Code to run on each cell
> If rCell.Value = "0" Then
> rCell.Interior.ColorIndex = 3
> Else
> rCell.Interior.ColorIndex = xlAutomatic
> End If
> Next rCell
>
> 'Format yellow (total) rows
> Range("D14:BL14,D26:BL26,D37:BL37,D47:BL47,D57:BL57,D68:BL68,D79:BL79").Select
> Selection.Interior.ColorIndex = 6
> Selection.HorizontalAlignment = xlRight
> Selection.VerticalAlignment = xlCenter
> Selection.NumberFormat = "#,##0.00_);(#,##0.00)"
>
> 'Move window back to the top
> ActiveWindow.SmallScroll Down:=-67
>
> 'and select cell
> Range("A1").Select
> End Sub
> --------------------
>
>
> --
> Desert Piranha
>
>
> ------------------------------------------------------------------------
> Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
> View this thread: http://www.excelforum.com/showthread...hreadid=560574
>
>

**Desert Piranha** · 07-13-2006, 12:17 PM

Originally Posted by crazybass2

Desert,

From you initial post I thought you wanted this action to occur when data
was pasted into the cells. It appears now that you what this action to occur
only when "aTest()" is run. The code is basically the same, but you need to
remove the intersect statement.

Assuming that "aTest()" is in the Sheet module for the sheet in question....

Sub aTest()
Dim rng As Range
For Each rng in Range("D3:BD3")
If rng <> "" Then Columns(rng.Column).Hidden = False
Next rng
'The rest of your code goes here
End Sub

That should do it. If "aTest()" is in another module some modification will
need to be done, let me know where it is and what sheet you are modifying if
this is the case

Mike

Hi Mike,

I'm really sorry about the confusion.

It pasted it into my "aTest()" and it is working good, at this point.
I have to go to a meeting but will test some more tonight, but i see no problems.

"aTest()" is in a general module and is called from another macro in the same module.
The first Macro is called from a text box on "Sheet6".
The first code copys data from a open workbook then pastes to "Sheet6".
"aTest()" reformats the area where the paste was done on "Sheet6".

Thank you very much for your knowledge & help.

crazybass2 · 07-13-2006, 01:50 PM

Desert,

Given your note about the general module, I have the following modification
to ensure this macro always works. Having the code snippet in the general
module will use the range ("D3:BD3") on the active sheet. If for any reason
"Sheet6" is not active, this code will not unhide the columns on sheet6. To
correct this you need to add 'Sheets("Sheet6"). before "Range" and "Columns"
So the revised code will be as follows:

Sub aTest()
Dim rng As Range
For Each rng in Sheets("Sheet6").Range("D3:BD3")
If rng <> "" Then Sheets("Sheet6").Columns(rng.Column).Hidden = False
Next rng
'The rest of your code goes here
End Sub

That should work regardless of which sheet is active.

Mike
"Desert Piranha" wrote:

>
> crazybass2 Wrote:
> > Desert,
> >
> > From you initial post I thought you wanted this action to occur when
> > data
> > was pasted into the cells. It appears now that you what this action to
> > occur
> > only when "aTest()" is run. The code is basically the same, but you
> > need to
> > remove the intersect statement.
> >
> > Assuming that "aTest()" is in the Sheet module for the sheet in
> > question....
> >
> > Sub aTest()
> > Dim rng As Range
> > For Each rng in Range("D3:BD3")
> > If rng <> "" Then Columns(rng.Column).Hidden = False
> > Next rng
> > 'The rest of your code goes here
> > End Sub
> >
> > That should do it. If "aTest()" is in another module some modification
> > will
> > need to be done, let me know where it is and what sheet you are
> > modifying if
> > this is the case
> >
> > MikeHi Mike,
>
> I'm really sorry about the confusion.
>
> It pasted it into my "aTest()" and it is working good, at this point.
> I have to go to a meeting but will test some more tonight, but i see no
> problems.
>
> "aTest()" is in a general module and is called from another macro in
> the same module.
> The first Macro is called from a text box on "Sheet6".
> The first code copys data from a open workbook then pastes to
> "Sheet6".
> "aTest()" reformats the area where the paste was done on "Sheet6".
>
> Thank you very much for your knowledge & help.
>
>
> --
> Desert Piranha
>
>
> ------------------------------------------------------------------------
> Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
> View this thread: http://www.excelforum.com/showthread...hreadid=560574
>
>

crazybass2 · 07-12-2006, 10:25 AM

Place this code in the module for the sheet in question.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Not Intersect(Target, Range("D3:BD3")) Is Nothing Then
For Each rng In Range("D3:BD3")
If rng <> "" Then Columns(rng.Column).Hidden = False
Next rng
End If
End Sub

Mike

"Desert Piranha" wrote:

>
> Hi all,
>
> I have columns D through BD where data is pasted from another workbook,
> one column per week (via code).
>
> So unused columns on the right are hidden. They are not completly
> unused as there are rows with formulas..
>
> Based on row 3 I am trying to:
> When the data (for this week as example) is pasted in then that coulmn
> will be unhidden.
>
> I am trying to change the 'End(xlToLeft)" to work with 'Columns D
> through BD' only.
> Cells(3, Columns.Count).End(xlToLeft).Offset(0, 1).EntireColumn.Hidden
> = False
>
> I can't use that, because columns to the right, BE through BF have data
> and that is blocking.
>
> Any direction is surely appreciated.
>
>
> --
> Desert Piranha
>
>
> ------------------------------------------------------------------------
> Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
> View this thread: http://www.excelforum.com/showthread...hreadid=560574
>
>