Error while deleting columns using excel vba

Question

sub column_delete()

dim x as workbook
dim sht1 as worksheet
dim currentColumn as integer
dim columnHeader as String

Set x = Workbooks.Open("Path to the file")
Set sht1 = x.Sheets("Page 1")
*For currentColumn = sht1.UsedRange.Columns.Count To 1 Step -1*

*columnHeader = shtl.UsedRange.Cells(1, currentColumn).Value*

Select Case columnHeader
 Case "Date", "Time", "Header" 
  'Nothing'
 Case Else
  *shtl.Columns(currentColumn).Delete*

End Select
Next

End Sub

For all the above highlighted steps i am getting error as Run-time error ‘424’: Object required.

But i believe i have declared the required object. Sometimes, when i reopen excel the error goes and other time it appears.

Can you please help me with this issue.

P.S : I am a beginner in excel vba.

sht1(with an 'one') is not the same as shtl (with a 'L'). Always use Option Explicit at the start of module to see those annoying typos. — Vincent G
– Vincent G, Commented Apr 16, 2018 at 9:53

Vityata · Accepted Answer · 2018-04-16 09:54:14Z

2

Always write Option Explicit on the top of your module.

Thus, errors like sht1 and shtl will be avoided, as far as you get an error on compile time with the nondefined variable highlighted:

answered Apr 16, 2018 at 9:54

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Error while deleting columns using excel vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related