Sleeper: Autofit without unhiding

[lior03]

hello
i can autofit a worksheet with the following macro:

Columns.autofit
Rows.autofit
by using this macro i am unhiding all hidden columns or rows.
i there a way to avoid this
thanks

[Bob Phillips]

Mine seemed to stay hidden, isn't there anything else going on?

[lior03]

hello
is it possible to make excell work only on visible cells.
in this case autofit them?
thanks

[CBrine]

xld,
I found that if the column is empty of data, it stays hidden, as soon as you put something in the hidden column the autofit does expand the column and 'UnHide' the column.

lior03,
I can't think of any easy way around this. My only suggestion would be to programmatically hide all the columns again, after the autofit. Here's the code you will need to execute for each hidden column. You can do ranges like ("A:C").



'For columns
ActiveSheet.Range("B:B").EntireColumn.Hidden = True
'For Rows
activesheet.range("1:1").entirerow.hidden = true


HTH
Cal

[Bob Phillips]

So it does. How strange!

[Simon Lloyd]

Hi, i am of course stating the obvious here but i'm never one to be left out!:



Sub fitandhide()
Application.ScreenUpdating = False
Columns.AutoFit
Rows.AutoFit
ActiveSheet.Range("B:C").EntireColumn.Hidden = True''''hides Columns B-C
ActiveSheet.Range("3:5").EntireRow.Hidden = True''''Hides Rows 3-5
Application.ScreenUpdating = True
End Sub


this will do it, although no worksheet is specified so will run on your activeworksheet!

Regards,
Simon

[Zack Barresse]

Yes, you're quite right, and that is a natural order of events to unhide hidden rows/columns. To leave the hidden cells hidden, you need to use specialcells as mentioned. Look at these two (first one works for your situation)..


Sub AutoFitAll()
On Error Resume Next
With Cells.SpecialCells(xlCellTypeVisible)
.EntireColumn.AutoFit
.EntireRow.AutoFit
End With
End Sub

Sub AutoFitAll2()
Cells.EntireColumn.AutoFit
Cells.EntireRow.AutoFit
End Sub

[lior03]

hello
thr=e following macro loop through all sheets if a sheet is empty it will be deleted if not it have it's cells autofited.how can i disable it from either llooping hidden sheets or invisible ranges.


Dim wb, wb1 As Worksheet
For Each wb In ActiveWorkbook.Worksheets
Set wb1 = ActiveSheet
wb.Activate
MsgBox " processing sheet : " & wb.name, _
vbExclamation, Format(Now, "dd/mm/yyyy hh:mm:ss")
If Application.WorksheetFunction.CountA(wb.Cells) = 0 Then
Application.DisplayAlerts = False
wb.Delete
Else
Columns.autofit
Rows.autofit
wb1.Activate
End If
Next

thanks.

[lior03]

maybe:


Dim wb, wb1 As Worksheet
For Each wb In ActiveWorkbook.Worksheets
If wb.Visible = xlSheetVisible Then
Set wb1 = ActiveSheet
wb.Activate
MsgBox " processing sheet : " & wb.name, _
vbExclamation, Format(Now, "dd/mm/yyyy hh:mm:ss")
If Application.WorksheetFunction.CountA(wb.Cells) = 0 Then
Application.DisplayAlerts = False
wb.Delete
Else
With Cells.SpecialCells(xlCellTypeVisible).Select
Columns.autofit
Rows.autofit
wb1.Activate
End With
End If
End If
Next
End Sub

[Zack Barresse]

moshe, just a couple of comments on your code...


'wb is dimensioned as a variant, because you do not specify what it is
'you also do not set that variable before you start using it
Dim wb, wb1 As Worksheet
'an example of what the line should look like..
'Dim wb As Workbook, wb1 As Worksheet 'although I'd use ws instead of wb1
'Set wb = ActiveWorkbook
For Each wb In ActiveWorkbook.Worksheets
'This is not right. here, your 'wb' variable should be set to the workbook, _
'and you should use wb1, or I would use ws, as it is less confusing, but _
'since you use wb throughout, that is what I'll comment on
If wb.Visible = xlSheetVisible Then
Set wb1 = ActiveSheet
'there is no need to even set wb1 variable, you already have it as wb!
wb.Activate
'there is no need to activate anything. you have the worksheet in variable _
'form, just work with it. hardly any need to activate/select.
MsgBox " processing sheet : " & wb.name, _
vbExclamation, Format(Now, "dd/mm/yyyy hh:mm:ss")
If Application.WorksheetFunction.CountA(wb.Cells) = 0 Then
Application.DisplayAlerts = False
wb.Delete
Else
With Cells.SpecialCells(xlCellTypeVisible).Select
'you do not reference the worksheet variable here, so it will be acting upon _
'the activesheet. of course you activate the sheet earlier in code, but best _
'practices dictates that we do not need to do that and it really just wastes _
'resources.
Columns.autofit
Rows.autofit
wb1.Activate
'instead of these (and the With statement, I would use something like...
' wb.Cells.SpecialCells(xlCellTypeVisible).Columns.EntireColumn.Autofit
' wb.Cells.SpecialCells(xlCellTypeVisible).Rows.EntireRow.Autofit
End With
End If
End If
Next
End Sub
Hope you don't mind. :)