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

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.

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

Please Help.

Chirag Raval
Chirag RavalAsked:
Who is Participating?
Rgonzo1971Connect With a Mentor Commented:

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

ShumsConnect With a Mentor Distinguished Expert - 2017Commented:
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
Application.ScreenUpdating = True
End Sub

Open in new window

Chirag RavalAuthor Commented:
Dear All Experts,

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

Chirag Raval
Solutions according to author's comment
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.