Trouble with hidden data

[Oreg]

Hello,

Here's the situiation. I have a sheet with vlookup functions whic
displays data from another sheet. Often the data displayed is prett
large . When data is entered into cells that vlookup is retrieving
the cells automatically expand to show all data entered. When vlooku
displays that same data on the other sheet, the cells don't expand.
have to manually resize the cell to fix. Is there a way around thi
problem??


Thanks

 

[Dave Peterson]

maybe autofit the rows and columns after each calculation?

rightclick on the worksheet tab that should behave this way.
Select view code and paste this in:

Option Explicit
Private Sub Worksheet_Calculate()
With Me.UsedRange
.Columns.AutoFit
.Rows.AutoFit
End With
End Sub

Works ok if you don't use merged cells!

 

[Oreg]

Thanks ! that works. Brings me to another question. What if the widt
of 2 columns were 43.00 /394 pixels and you wanted to keep them at tha
width only. All other columns are fine. Is there a way to specif
this?
Thanks again for your tim

 

[Dave Peterson]

I'd reset all of them with that other code and then come back and reset these
two:

Option Explicit
Private Sub Worksheet_Calculate()
With Me.UsedRange
.Columns.AutoFit
.Rows.AutoFit
End With
Me.Range("c1,f1").ColumnWidth = 43
End Sub

 

[Dave Peterson]

You could have broken you columns into smaller pieces, too:

Option Explicit
Private Sub Worksheet_Calculate()
Me.Columns("A:B").AutoFit
Me.Columns("D:E").AutoFit
Me.Columns("G:IV").AutoFit
Me.Range("c1,f1").ColumnWidth = 32
End Sub

 

[Oreg]

Thanks again...works great