How write to hidden columns with VBA?

B

Bob Phillips

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 said:
Is there a way to write to hidden columns from vba without unhiding them
Click to expand...
first?
 
T

Tom Ogilvy

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

--
Regards,
Tom Ogilvy

Michel said:
Is there a way to write to hidden columns from vba without unhiding them
Click to expand...
first?
 
M

Michel

Bob Phillips said:
The fact that it is hidden should not stop you.
What problem are you experiencing?
Click to expand...

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

Tom Ogilvy

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