Autofit VBA question

helpme20

Board Regular
Joined
Aug 28, 2010
Messages
95
I am trying to write a VBA formula using the Workbook Sheetchange formula and I need some help please.

Private Sub Workbook_Open()

End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Columns().AutoFit
End Sub

I only want it to work with one of my sheets. it is named ProductFinder and I only want it to work for the data in cells, $Q$6:$W$104 (BTW I have named that cell range "Overall" if that helps)



Can someone help me with this? Thank you!
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,997
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Rng As Range
   Set Rng = Intersect(Target, Range("Q6:W104"))
   If Not Rng Is Nothing Then Rng.Columns.AutoFit
End Sub
This needs to go in the ProductFinder sheet module.
 

helpme20

Board Regular
Joined
Aug 28, 2010
Messages
95
How about
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Rng As Range
   Set Rng = Intersect(Target, Range("Q6:W104"))
   If Not Rng Is Nothing Then Rng.Columns.AutoFit
End Sub
This needs to go in the ProductFinder sheet module.


How do I add it in the module that is just for Sheet 4 that is named ProductFinder?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,997
Office Version
  1. 365
Platform
  1. Windows
Just paste it into the code module for that sheet.
 

helpme20

Board Regular
Joined
Aug 28, 2010
Messages
95

ADVERTISEMENT

How do I add it in the module that is just for Sheet 4 that is named ProductFinder?

I can double click on Sheet 4 in Microsoft Excel Objects and then add the code.

When I do that, it doesn't automatically do the autofit?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,997
Office Version
  1. 365
Platform
  1. Windows
It will, whenever you change (manually or by code) a cell in the range Q6:W104
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,928
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

When exactly do you want the code to run?

When someone changes a value in that range?
When the workbook is opened?
When that sheet is activated?
 

helpme20

Board Regular
Joined
Aug 28, 2010
Messages
95
When exactly do you want the code to run?

When someone changes a value in that range?
When the workbook is opened?
When that sheet is activated?

I have data in a separate worksheet and I have a cell in the Product Finder sheet that when you enter anything in Cell T4, it returns specific data in Q thru W Columns.

It may return 1 row of data based on what you entered in T4, but it also could return 40 rows of data. I need whatever it returns to Autofit to the Column Width and Row Height.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,928
Office Version
  1. 365
Platform
  1. Windows
OK, then put this VBA code on this sheet where you are entering the value into T4:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("T4")) Is Nothing Then
        Sheets("ProductFinder").Range("Q6:W104").Columns.AutoFit
    End If
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,997
Office Version
  1. 365
Platform
  1. Windows
In that case use
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address(0, 0) = "T4" Then
      Range("Q6:W104").Columns.AutoFit
   End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,657
Messages
5,597,391
Members
414,142
Latest member
Banyangt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top