CODEWITHSUNDEEP
excelvba
user2664304
user2664304

Reputation: 35

excel vba macro delete column based on cell content

I'm trying to write a macro to delete columns from a spreadsheet if they have certain content. All the data is in the first row, of variable length. I think the problem may have to do with my range selection. I keep getting subscript out of range when I try to do the search. Any advice would be appreciated. :)

Sub Disk_Firmware()
   Dim c As Range
   Dim SrchRng As Range
   Dim SrchStr As String
   Set SrchRng = ActiveSheet.Range("A1").EntireRow
   SrchStr = InputBox("Please enter a search string")
   Do
      Set c = SrchRng.Find(SrchStr, LookIn:=xlvalues)
      If Not c Is Nothing Then c.EntireColumn.Delete
   Loop While Not c Is Nothing
End Sub

Upvotes: 1

Views: 4818

Answers (1)

RestitutorOrbis
RestitutorOrbis

Reputation: 189

Your code works absolutely fine in my VBE. I just added the following:

SrchStr = Trim(SrchStr)

For that error, the MSDN website suggests that you use a For Next instead of a Do While, so as not to specify index elements.

Upvotes: 1

Related Questions