Deleting columns based on criteria in a specified row

PraRan

New Member
Joined
Feb 21, 2019
Messages
22
Office Version
  1. 365
Platform
  1. Windows
I'm trying to delete columns based on test in row1 of the worksheet. Here's an example of what I'm using.
I want to delete all columns that contain 'no'. There are 169 columns in this worksheet, with 130 of those with 'no'.

noYnoYYYYYnononoYYnoYnonoYYYYYYnono
Col 1Col 2Col 3Col 4Col 5Col 6Col 7Col 8Col 9Col 10Col 11Col 12Col 13Col 14Col 15Col 16Col 17Col 18Col 19Col 20Col 21Col 22Col 23Col 24Col 25

Here's the code that I've written to delete the columns. The code deletes only some of the columns with a no--only the 1st column of adjacent no will be deleted. For ex: Col 9, 10 and 11 have 'no'. Only 9 will delete but not Col 10 and 11.

Dim cell As Range
Sheet1.Activate
For Each cell In Sheet1.Range("A1:FM1").Cells
If cell.Value = "no" Then
cell.EntireColumn.Select
Selection.Delete
End If
Next cell

End Sub

What could be going wrong.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Are the values in row 1 hard values, or the result of a formula?
 
Upvote 0
When deleting rows or columns via loops, you need to work backwards, or else you miss some as deleting the range moves rows into the range you just checked.

Try this:
VBA Code:
Sub MyDeleteColumns()

    Dim c As Long

    Sheet1.Activate
    
    Application.ScreenUpdating = False
    
'   Loop through columns backwards
    For c = 169 To 1 Step -1
        If Cells(1, c).Value = "no" Then Cells(1, c).EntireColumn.Delete
    Next c

    Application.ScreenUpdating = True
    
End Sub

I think that there may be a way to do it without loops too, which would be even faster.
 
Upvote 0
When deleting rows or columns via loops, you need to work backwards, or else you miss some as deleting the range moves rows into the range you just checked.

Try this:
VBA Code:
Sub MyDeleteColumns()

    Dim c As Long

    Sheet1.Activate
   
    Application.ScreenUpdating = False
   
'   Loop through columns backwards
    For c = 169 To 1 Step -1
        If Cells(1, c).Value = "no" Then Cells(1, c).EntireColumn.Delete
    Next c

    Application.ScreenUpdating = True
   
End Sub

I think that there may be a way to do it without loops too, which would be even faster.
Yeah, without loops would be faster. And without macros too--if I do a find 'no' and Ctrl+A, it selects all cells with 'no', right click, delete, entire column. this will also achieve the same goal without macros. But want to add macros so I don't have to think about it every time, which is 2x per week.
 
Upvote 0
When deleting rows or columns via loops, you need to work backwards, or else you miss some as deleting the range moves rows into the range you just checked.

Try this:
VBA Code:
Sub MyDeleteColumns()

    Dim c As Long

    Sheet1.Activate
   
    Application.ScreenUpdating = False
   
'   Loop through columns backwards
    For c = 169 To 1 Step -1
        If Cells(1, c).Value = "no" Then Cells(1, c).EntireColumn.Delete
    Next c

    Application.ScreenUpdating = True
   
End Sub

I think that there may be a way to do it without loops too, which would be even faster.
can you please help explain this statement " For c = 169 To 1 Step -1". I think I understand the 1st part but not Step -1.
 
Upvote 0
I
can you please help explain this statement " For c = 169 To 1 Step -1". I think I understand the 1st part but not Step -1.
In "For x = y to z" structured loops, if you leave off the optional "Step" part, the default is to count by ones in Ascending order.
However, you could also count by factors other than 1, i.e. if your wanted to count by 2s, you could do something like:
VBA Code:
For x = 2 to 10 Step 2

However, we want to count backwards by 1s. So you have to use a negative sign to count backwards, hence:
VBA Code:
For c = 169 to 1 Step -1

Make sense?

Since you only have 169 columns, it doesn't take real long, even with the loop.
 
Upvote 0
When deleting rows or columns via loops, you need to work backwards, or else you miss some as deleting the range moves rows into the range you just checked.

Try this:
VBA Code:
Sub MyDeleteColumns()

    Dim c As Long

    Sheet1.Activate
   
    Application.ScreenUpdating = False
   
'   Loop through columns backwards
    For c = 169 To 1 Step -1
        If Cells(1, c).Value = "no" Then Cells(1, c).EntireColumn.Delete
    Next c

    Application.ScreenUpdating = True
   
End Sub

I think that there may be a way to do it without loops too, which would be even faster.
This solution didn't work either, the same issue. where the code deletes the 1st No in adjacent columns and skips the next.
 
Upvote 0
This solution didn't work either, the same issue. where the code deletes the 1st No in adjacent columns and skips the next.
It shouldn't, but I think it may be due to your formulas. Deleting the column does not delete the sources where the formulas are calling from, so you could be having some unexpected behavior.
The deleting of columns moves all the columns to the right of it over, so subsequent deletions are affecting the ranges you already addressed.

Try stepping through your code (by putting a breakpoint on the "For" loop line), and watch what happens as you go through column-by-column.
I think you may see that everything to the right of where you are looks fine, and then a column is deleted, and it messes up those columns you already addressed.

If you cannot figure it out, your best bet would probably be to upload your file to a file sharing site and provide us a link so we can see it and test it out for ourselves.
Just be sure to "dummy up" any sensitive data first.
 
Upvote 0
It shouldn't, but I think it may be due to your formulas. Deleting the column does not delete the sources where the formulas are calling from, so you could be having some unexpected behavior.
The deleting of columns moves all the columns to the right of it over, so subsequent deletions are affecting the ranges you already addressed.

Try stepping through your code (by putting a breakpoint on the "For" loop line), and watch what happens as you go through column-by-column.
I think you may see that everything to the right of where you are looks fine, and then a column is deleted, and it messes up those columns you already addressed.

If you cannot figure it out, your best bet would probably be to upload your file to a file sharing site and provide us a link so we can see it and test it out for ourselves.
Just be sure to "dummy up" any sensitive data first.
I've converted the formula into value and still no luck. And I've stepped through the code to try and identify where its breaking, but can't figure it out. For now I'm hiding the No columns and copying visible cells only to a new sheet. that's a fix to help move things forward for me, however I'd still like to understand why the code isn't working to delete columns. Maybe another time.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top