VBA or Format to Expand all cells after each input

[c0087]

I want all cells in a worksheet to expand after every input as if you had selected them all, then double clicked the grid line to manually expand them. Thanks in advance

 

[c0087]

I assume you know all cells in a column must be the same width.

Range("A1") cannot be 2 mm wide and Range("A3") be 6mm wide

all the cells in each column are the same size, but not all columns are the same width

 

[c0087]

MAIT
Maybe the OP hasn't modified your last snippet to be a Worksheet_change event.

Also try

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  6/4/2019  2:14:17 AM  EDT
 With ActiveSheet.UsedRange
 .Columns.AutoFit
 End With
End Sub

nailed it BOSS. Much thanks to both you guys

 

[Michael M]

Glad we could help...

 

[My Aswer Is This]

Well this does work but your putting your worksheet to work for sure.
If you have 30,000 cells with data this script will have to run through all 30,000 cells every time you change any value on your sheet. So you may experience some performance issues.

But if your happy then that's what counts.

 

[c0087]

Well this does work but your putting your worksheet to work for sure.
If you have 30,000 cells with data this script will have to run through all 30,000 cells every time you change any value on your sheet. So you may experience some performance issues.

But if your happy then that's what counts.

It's about 250 cells so no performance problems so far. I don't know why your second solution was working for you and not me. Your first solution would've worked as my backup had I not been able to get my ideal solution. Thanks again for your time and help

 

[Michael M]

MAIT, good comment...I assumed a reasonable dataset wouldn't be an issue.
This might be better, although I haven't tested for speed !!

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim lc As Long
 lc = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
 With ActiveSheet.Range(Cells(1, 1), Cells(1, lc))
 .Columns.AutoFit
 End With
End Sub

 

[c0087]

MAIT, good comment...I assumed a reasonable dataset wouldn't be an issue.
This might be better, although I haven't tested for speed !!

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim lc As Long
 lc = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
 With ActiveSheet.Range(Cells(1, 1), Cells(1, lc))
 .Columns.AutoFit
 End With
End Sub

Just tested this one out, but not all columns expanded. Your first solution still works the best

*FWIW* My data set is around 1500 cells (with formulas) and it works almost instantly