How write to hidden columns with VBA?
 

Is there a way to write to hidden columns from vba without unhiding them first?

How write to hidden columns with VBA?
 

The fact that it is hidden should not stop you.

What problem are you experiencing?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Michel" wrote in message
om...
Is there a way to write to hidden columns from vba without unhiding them
first?

How write to hidden columns with VBA?
 

Cells(1,10).Value = 1000
puts 1000 in Cell J1 whether the column is hidden or not

--
Regards,
Tom Ogilvy

"Michel" wrote in message
om...
Is there a way to write to hidden columns from vba without unhiding them
first?

How write to hidden columns with VBA?
 

"Bob Phillips" wrote:
The fact that it is hidden should not stop you.
What problem are you experiencing?

Correct, the problem is that it can't be found!
Set oFound = oWorksheet.Rows(1).Find(vColName, LookIn:=xlValues, LookAt:=xlWhole)

so, the error comes from trying to use the oFound which in Nothing

So I should have asked: How can I search in hidden columns?

How write to hidden columns with VBA?
 

This failed as you noted:

Sub tester1()
Rows(1).Value = "Z"
Range("M1").Value = "A"
Set oWorksheet = ActiveSheet
Range("A:AA").EntireColumn.Hidden = True
vColName = "A"
Set oFound = oWorksheet.Rows(1).Find(vColName, _
LookIn:=xlValues, LookAt:=xlWhole)
If Not oFound Is Nothing Then
Debug.Print oFound.Address
Else
Debug.Print "Not found"
End If

End Sub

This Worked - using xlFormulas rather than xlValues

Sub tester2()
Rows(1).Value = "Z"
Range("M1").Value = "A"
Set oWorksheet = ActiveSheet
Range("A:AA").EntireColumn.Hidden = True
vColName = "A"
Set oFound = oWorksheet.Rows(1).Find(vColName, _
LookIn:=xlFormulas, LookAt:=xlWhole)
If Not oFound Is Nothing Then
Debug.Print oFound.Address
Else
Debug.Print "Not found"
End If

End Sub

of course looking for something produced by a formula would not work.

--
Regards,
Tom Ogilvy

"Michel" wrote in message
m...
"Bob Phillips" wrote:
The fact that it is hidden should not stop you.
What problem are you experiencing?

Correct, the problem is that it can't be found!
Set oFound = oWorksheet.Rows(1).Find(vColName, LookIn:=xlValues,
LookAt:=xlWhole)

so, the error comes from trying to use the oFound which in Nothing

So I should have asked: How can I search in hidden columns?