Thread: Delete Column based on Column header

Try this, though I'm not entirely sure what you mean by

3. If that same (noted) row# in the "Weather" column has any data as the bottom-most with data in the "Cloudy" column, then delete the whole "Cloudy" column entirely.

Code:

Sub x()
 
Dim rFind1 As Range, rfind2 As Range

With Sheet1.Rows(1)
    Set rFind1 = .Find(What:="Cloudy", LookIn:=xlFormulas, _
                          LookAt:=xlWhole, SearchOrder:=xlByColumns, _
                          SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    Set rfind2 = .Find(What:="Weather", LookIn:=xlFormulas, _
                          LookAt:=xlWhole, SearchOrder:=xlByColumns, _
                          SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not rFind1 Is Nothing And Not rfind2 Is Nothing Then
        If Cells(Rows.Count, rFind1.Column).End(xlUp) _
            = Cells(Rows.Count, rfind2.Column).End(xlUp) Then Columns(rfind2.Column).Delete
    End If
End With
        
End Sub

There is a small change in Stephen's code. Using his code deletes the 'Weather' column instead of the 'Cloudy' column. Just make this small change in the last but 4th line:

Code:

= Cells(Rows.Count, rfind2.Column).End(xlUp) Then Columns(rfind1.Column).Delete

Though even I am not sure about what you mean by:

3. If that same (noted) row# in the "Weather" column has any data as the bottom-most with data in the "Cloudy" column, then delete the whole "Cloudy" column entirely.

--Karan--

Thank you karan.

Thanks guys,

Hmmm, nothing happened at all. Not sure why.

The other thing I forgot to mention is that I would like to macro to do this on the active sheet as well and not sheet 1.

I changed the line of code from

Code:

With Sheet1.Rows(1)

to

Code:

With ActiveSheet.Rows(1)

Was that part correct?



As far as the macro, let me try and explain simpler. I realize it may have been a bit confusing (and it still may be)

Let's say that there is data of some sort in each row of the "cloudy" column from row 2 down to row 100 (row 1 is the header row). Providing there is some sort of data in rows 2 to 100 row of the "weather" column, then delete the entire "cloudy" column.

However, if there are any blank rows in the "cloudy" column between rows 2 and row 100, then don't even bother checking the "weather" column and leave the "cloudy" column alone.


I hope that's a bit easier to explain

Thanks much

That doesn't sound much like your original question. See if this works, still not quite sure...

Code:

Sub x()
 
Dim rFind1 As Range, rfind2 As Range

With Rows(1)
    Set rFind1 = .Find(What:="Cloudy", LookIn:=xlFormulas, _
                          LookAt:=xlWhole, SearchOrder:=xlByColumns, _
                          SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    Set rfind2 = .Find(What:="Weather", LookIn:=xlFormulas, _
                          LookAt:=xlWhole, SearchOrder:=xlByColumns, _
                          SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not rFind1 Is Nothing And Not rfind2 Is Nothing Then
        If WorksheetFunction.CountBlank(Cells(1, rFind1.Column).Resize(99)) = 0 And _
            WorksheetFunction.CountBlank(Cells(1, rfind2.Column).Resize(99)) = 0 Then Columns(rFind1.Column).Delete
        End If
    End If
End With
        
End Sub

Sorry this one is so confusing. The error I got now says...

"Compile Error"
"End if without block if"


I think this might be the best way to explain it....


Basically, I just want to delete the "Cloudy" column if there are no blank cells equal to the bottom-most cell with data in the "Weather" column.



I hope that makes it easier and thanks again. I won't be able to check this out right away but I do look forward to having this one figured out.

Thanks for your patience.

For the error, you just need to remove one of the "End If" statements - my mistake.

If that doesn't solve the problem, please can you attach a workbook illustrating what you want doing because your descriptions don't seem to me to marry up.

Thanks for all the help. Attached is a sample.

I ran the "x" macro on sheet1. The "Cloudy" column wasn't deleted even though it had data in it down to the same row as the "Weather" column.

I then tried running the "x" macro on sheet2, (just for kicks).

In Sheet2, the "Cloudy" column DOES have blanks. So, in this case, the "Cloudy" column should NOT be deleted, which it wasn't.

Thanks

I assumed, stupidly, that your data was in rows 1 to 100 as you said. The fact that it wasn't makes a difference.

Code:

Sub x()
 
Dim rFind1 As Range, rfind2 As Range, n1 As Long, n2 As Long

With Rows(1)
    Set rFind1 = .Find(What:="Cloudy", LookIn:=xlFormulas, _
                          LookAt:=xlWhole, SearchOrder:=xlByColumns, _
                          SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    Set rfind2 = .Find(What:="Weather", LookIn:=xlFormulas, _
                          LookAt:=xlWhole, SearchOrder:=xlByColumns, _
                          SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not rFind1 Is Nothing And Not rfind2 Is Nothing Then
    n1 = Cells(Rows.Count, rFind1.Column).End(xlUp).Row
    n2 = Cells(Rows.Count, rfind2.Column).End(xlUp).Row
        If WorksheetFunction.CountBlank(Cells(1, rFind1.Column).Resize(n1)) = 0 And _
            WorksheetFunction.CountBlank(Cells(1, rfind2.Column).Resize(n2)) = 0 Then Columns(rFind1.Column).Delete
    End If
End With
        
End Sub

Stephen,

Thanks soooo much, that worked beautifully!

My apologies for not making it clearer when I brought up the "100" row thing. In the process of trying to make easier to understand by mentioning a specific row number, I further confused the situation. It's all good now though.

Thanks Again!

Glad we got there in the end!