VBA to select column by name and set custom width

wzthxj

New Member
Joined
Mar 29, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
I'm using a script I found in a post from RickXL to auto-find the last column and add header name. This worked perfectly, and now I want to set the width of each new column to the values shown. I've tried with and without the Columns.EntireColumn.Select method, along with Columns.ColumnWidth = ** but no matter which combination I use, all columns are resized to 10. I'm a beginner and would appreciate any help possible. I also have some other actions that I would like to perform on the last 3 new columns which I've already created via another macro, but am unsure how to insert them into this one correctly. Thank you!

Sub InsertColumnAfterLastColumn()
Dim LastRow As Long
Dim LastCol As Long
Dim iRow As Long
Set ws = Sheet4

With ws
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
.Cells(1, LastCol + 1).Value = "Observations"
Columns.EntireColumn.Select
Columns.ColumnWidth = 30

LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
.Cells(1, LastCol + 1).Value = "Deviations"
Columns.ColumnWidth = 20

LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
.Cells(1, LastCol + 1).Value = "Potential Debit Amount"
Columns.ColumnWidth = 10

LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
.Cells(1, LastCol + 1).Value = "Debit Amount"
Columns.ColumnWidth = 10

End With
End Sub
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,135
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub wzthxj()
   
   With Sheet4.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).Resize(, 4)
      .Value = Array("Observations", "Deviations", "Potential Debit Amount", "Debit Amount")
      .ColumnWidth = 10
   End With
End Sub
 

wzthxj

New Member
Joined
Mar 29, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Thanks for the reply! It looks like this will adjust all 4 to 10; what I need is 30, 20, 10, 10 due to content that will be added. Other thoughts?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,135
Office Version
  1. 365
Platform
  1. Windows
Missed that bit, try
VBA Code:
Sub wzthxj()
   
   With Sheet4.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).Resize(, 4)
      .Value = Array("Observations", "Deviations", "Potential Debit Amount", "Debit Amount")
      .Resize(, 1).ColumnWidth = 30
      .Offset(, 1).Resize(, 1).ColumnWidth = 20
      .Offset(, 2).Resize(, 2).ColumnWidth = 10
   End With
End Sub
 
Solution

wzthxj

New Member
Joined
Mar 29, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
That worked perfectly, thank you! One last question; the new column "Deviations" contains a drop down menu in each cell. I need to somehow associate the drop-down with this column, with the caveat that the number of columns before the 4 new ones just created varies by user. I created a macro but it's currently associated with a specific cell (which will change). Thanks for your help.

Range ("**").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Data!$A$2:$A$11"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,135
Office Version
  1. 365
Platform
  1. Windows
As this is a totally different question, it needs a new thread. Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,132,644
Messages
5,654,552
Members
418,140
Latest member
ahepple86

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