VB to delete columns with value in specifc cell [SOLVED]

[mr_hiboy]

Hi,


Hope you can help.


Looking to create a simple script to select columns E:AF and then delete any columns that do not have "Yes" in row 7 of that column.


e.g. Column G, cell G7=Yes, so don't delete it. H7=0 therefore delete it.


Thanks in advance

 

[mr_hiboy]

I'm nearly there with it, if someone can help me over the line that would be great.


It starts to delete the columns, but then stops and "mismatch error" highlighting If Not Cell = "Yes" Then


Dim Rng As Range

Dim Cell As Range


Set Rng = Sheets("Sheet1").Range("$F$7:$AG$7")


For Each Cell In Rng

If Not Cell = "Yes" Then

Cell.EntireColumn.Delete


End If

Next Cell

 

[anupamtiwari05]

Hi mr_hiboy,


Try the below code, this can help you to fullfil your requirement.

[pre]

Sub DeleteColumns()
lc = Worksheets("Sheet1").Range("1:1").End(xlToRight).Column
For i = lc To 1 Step -1
If Cells(7, i).Text <> "Yes" Then
Columns(i).EntireColumn.Delete
End If
Next

[/pre]
End Sub

Please let me know if you find any issue with above solution.


Thanks & Regards,

Anupam Tiwari

 

[mr_hiboy]

Hi, thanks for the response.


Didn't work, deleted my first few columns. I need to start checking from cell F7.


It also didn't delete the remaining columns that weren't ="yes"


thanks

 

[anupamtiwari05]

Hi mr_hiboy,


Try the below code

[pre]

Sub DeleteColumns()
lc = Worksheets("Sheet1").Range("1:1").End(xlToRight).Column
For i = lc To 1 Step -1
If Cells(7, i).Text <> "Yes" And Cells(7, i).Text <> "yes" Then
Columns(i).EntireColumn.Delete
End If
Next
End Sub

OR


Sub DeleteColumns()
lc = Worksheets("Sheet1").Range("1:1").End(xlToRight).Column
For i = lc To 1 Step -1
If Ucase(Cells(7, i).Text) <> "YES" Then
Columns(i).EntireColumn.Delete
End If
Next

[/pre]
End Sub


Thanks & Regards,

Anupam Tiwari

 

[mr_hiboy]

Neither work.


I'm nearly here with this. But after it deletes the column, i need it to step back a column, as if there is two columns together it skips one.


Dim Rng As Range

Dim Cell As Range


Set Rng = Sheets("Sheet1").Range("F7:aZ7")

For Each Cell In Rng


If Cell = "No" Then

Cell.EntireColumn.Delete


End If

Next Cell

End Sub

 

[NARAYANK991]

Hi ,


Try this :

[pre]

Sub DeleteColumns()
Const CHECK_ROW = 7
Const CHECK_TEXT = "YES"
Const CHECK_COLUMNS = "$F:$AG"

Number_of_Columns = Range(CHECK_COLUMNS).Columns.Count

With Range(CHECK_COLUMNS)
Do While Number_of_Columns >= 0
If UCase(.Cells(CHECK_ROW, Number_of_Columns).Value) <> CHECK_TEXT Then
.Cells(CHECK_ROW, Number_of_Columns).EntireColumn.Delete
End If
Number_of_Columns = Number_of_Columns - 1
Loop
End With
End Sub

[/pre]
Narayan

 

[mr_hiboy]

Sorted with this (made a few tweaks to the yes/no helper column). Thanks for replying.


Dim i

For i = 5 To 33


If Sheets("Sheet1").Cells(7, i) = "No" Then

Sheets("Sheet1").Range(Cells(7, i), Cells(7, i)).EntireColumn.Delete

i = i - 1

End If


Next

 

[NARAYANK991]

Hi ,


Please note that the kind of constructs you are using are not recommended in programming !


The counter used in a FOR ... NEXT loop is not to be modified within the loop , since the FOR ... NEXT loop itself takes care of the incrementing or decrementing.


If you wish to use a FOR ... NEXT loop where the counter decrements , use it as follows :

[pre]

For i = 33 to 5 Step -1
.
.
.
Next

[/pre]
without using the i = i - 1 line.


Narayan

 

[mr_hiboy]

Thanks for that.

 

[SirJB7]

Hi, mr_hiboy!


Just as a tip, whenever deleting elements from a group (array, range, rows, columns, collection, ...), i.e. anything that gets resized after each delete operation the recommended structure is something like this:

-----

[pre]

Sub DeleteElementsFromCollections()
Dim vCollection as XXXX, lMaxCount As Long, lIndex As Long
lMaxCount = <vCollection.Elements.Count>/Ubound(vCollection)
For lIndex = lCounter To 1 Step -1
If "<condition>" Then
<Remove element lIndex from vCollection>
End If
Next lIndex
End Sub

[/pre]
-----


Regards!