Column Auto Width and Hidden Rows

[Guest]

I have a macro that selects certain columns and auto sets the width.
The problem is it is using data in rows that are hidden.
How can I make it ignore these rows when selecting the width?
Thanks,
KWCounter

 

[Dave Peterson]

I'm not sure you can.

But you could copy that column (visible cells only) to a new sheet (in column
A), autofit that column, keep track of the width and use it on the original
column.

Option Explicit
Sub testme()
Dim curWks As Worksheet
Dim tempWks As Worksheet
Dim rng As Range
Dim myWidth As Double

Set curWks = ActiveSheet '???
Set tempWks = Worksheets.Add

With curWks
Set rng = Nothing
On Error Resume Next
Set rng = .Range("a:a").Cells.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If rng Is Nothing Then
'no visible cells, what to do?
Else
rng.Copy _
Destination:=tempWks.Range("a1")
tempWks.Range("a1").EntireColumn.ColumnWidth = 255
tempWks.Range("a1").EntireColumn.AutoFit

myWidth = tempWks.Range("a1").ColumnWidth
.Range("a1").ColumnWidth = myWidth
End If
End With

Application.DisplayAlerts = False
tempWks.Delete
Application.DisplayAlerts = True

End Sub