PDA

View Full Version : [SOLVED] First unhidden column



av8tordude
02-05-2014, 11:55 AM
Instead of setting the code to column "A", how can I set it to the first unhidden column from the left? Example if column A is hidden, then set column B or C etc. Thanks


For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))

av8tordude
02-06-2014, 03:25 AM
Any help on this request?

Aflatoon
02-06-2014, 03:36 AM
If you know row1 is visible:
Rows(1).Specialcells(xlcelltypevisible)(1).Column

westconn1
02-06-2014, 03:44 AM
try like

for each col in columns
if not col.hidden then exit for
next
For Each c In Range(cells(1, 1), cells(rows.count, col.column)).End(xlUp)

av8tordude
02-06-2014, 04:55 AM
Aflatoon...forgive me, but how do I apply it to my code?

westconn1, it seems to not accomplish the task. here's the code I'm using...


For Each c In Range("A1", Range("A1" & Rows.Count).End(xlUp))
Select Case c
Case "AL"
c = "Alabama"
Case "AZ"
c = "Arizona"
End select
next c


BTW, Column 1 is hidden, but I don't want to hard code column 1 because, the format could change to column 1 & 2 or 1,2, & 3, etc

av8tordude
02-06-2014, 06:28 AM
still seeking help. Thanks for your assistances.

Aflatoon
02-06-2014, 07:02 AM
Like this:
Dim lCol as Long
lCol = Rows(1).Specialcells(xlcelltypevisible)(1).Column
For Each c In Range("A" & lcol, Cells(Rows.Count, lCol).End(xlUp)).Cells
Select Case c.Value
Case "AL"
c.Value = "Alabama"
Case "AZ"
c.Value = "Arizona"
End Select
Next c

av8tordude
02-06-2014, 07:22 AM
Works perfect Aflatoon. Thank you! :-)