How to Delete all columns after last cell with data in row 1?

Chirag Raval
Chirag Raval used Ask the Experts™
on
Dear All experts,

I already use this link's Mr Patrick Matthews's suggested code for delete all rows below data provided at this link of this site.

https://www.experts-exchange.com/questions/27255636/Delete-All-Rows-after-Last-Row-with-Data.html

but same question for columns...how to delete all columns  after last filled data on row 1.
or how to modify above linked code to delete columns.

Please Help.

Regards,
Chirag Raval
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
Hi,

pls try
Sub clear()
   Dim lastCell As Range
   Set lastCell = Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious, searchorder:=xlByColumns)
   If Not lastCell Is Nothing Then Range(Cells(1, lastCell.Column + 1), Cells(Rows.Count, Columns.Count)).Delete
End Sub

Open in new window

EDIT corrected code

Regards
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018
Commented:
Hi Chirag,

Try below, change sheet name accordingly.
Sub DelCols()
Dim Ws As Worksheet
Dim LastC As Long
Dim DelRng As Range
Set Ws = Worksheets("Sheet1") ' Change your sheet name here
LastC = Ws.Cells(1, Columns.Count).End(xlToLeft).Column
Set DelRng = Ws.Range(Ws.Cells(1, LastC + 1), Ws.Cells(1, Columns.Count))
Application.ScreenUpdating = False
DelRng.EntireColumn.Delete
Application.ScreenUpdating = True
End Sub

Open in new window

Author

Commented:
Dear All Experts,

Great, Amazing Both works like a charm..
Thank you very much

Regards,
Chirag Raval
Top Expert 2016

Commented:
Solutions according to author's comment

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial