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

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

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 i
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:BL5 7,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 Su
-------------------

--
Desert Piranh

-----------------------------------------------------------------------
Desert Piranha's Profile: http://www.excelforum.com/member.php...fo&userid=2893
View this thread: http://www.excelforum.com/showthread.php?threadid=56057

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:BL5 7,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

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

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 Wrote:
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.

MikeHi mike,

Thank you. Everything is wonderful in Desert Land


--
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