Excel: Number of columns equal to cell value-CodePudding

I hope someone can point me in the right direction: I want to make the number of columns (only row 25 to 38) be equal to the number in cell C24. If cell C24 contains 3 then I want the total number of columns to be 3. I have tried a couple of ways and adding new columns alone goes alright but removing them crashes my sheet. I tried the code below.

Sub adding()
If LastCol = Range("C24").Value Then 
ElseIf LastCol < Range("C24").Value Then
  Range("C25:C36").Select
    Selection.Copy
    Selection.Insert Shift:=xlToRight
ElseIf LastCol > Range("C24").Value Then
  Range("D25:D36").Select
  Application.CutCopyMode = False
   Selection.Delete Shift:=xlToLeft
   End If
End Sub

CodePudding user response：

Have a try with the code below:

Sub Adding()

Dim col As Long, LastCol As Long

LastCol = Cells(25, Columns.Count).End(xlToLeft).Column - 2 'This auto finds last column using row 25.
col = Range("C24").Value

If LastCol < col Then
    Range("C25:C36").Copy
    Range("C25:C36").Resize(, Abs(LastCol - col)).Insert shift:=xlToRight
ElseIf LastCol > col Then
    Range("D25:D36").Resize(, Abs(LastCol - col)).Delete shift:=xlToLeft
Else
    'if equals do what you want
End If
Application.CutCopyMode = False

End Sub

Basically we grab the initial range (C25:C36 for inserting and D25:D26 for deleting) and use the Resize method to expand the range by the number of columns. I put in the calculation for the last column but if that's not right for you then we can see what is right. Because it starts in Column C I minus 2 off the LastCol.

I tested it and it did what I believe you want so hopefully works for you.Let me know.