• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

mr_hiboy

Member
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

Member
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
 
Hi mr_hiboy,


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

[pre]
Code:
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

Member
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
 
Hi mr_hiboy,


Try the below code

[pre]
Code:
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

Member
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

Excel Ninja
Hi ,


Try this :

[pre]
Code:
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

Member
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

Excel Ninja
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]
Code:
For i = 33 to 5 Step -1
.
.
.
Next
[/pre]
without using the i = i - 1 line.


Narayan
 

SirJB7

Excel Rōnin
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]
Code:
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!
 
Top