EXCEL If Column T is empty, Delete It

Tosagua
Tosagua used Ask the Experts™
on
I need some code for a macro:

If Column T is Empty, Delete column T.
If column T contains data, go to the next command (step).

Any assistance that can be provided is greatly apprreciated.

Tosagua
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
In your code, use something like this:

    If Application.CountIf([t:t]) = 0 Then [t:t].Delete

Author

Commented:
matthewspatrick:

I get:

Runtime Error 1004
Invalid Number Of Arguments.

Tosagua
Top Expert 2010

Commented:
Tosagua,

Sorry, wrong function:

    If Application.CountA([t:t]) = 0 Then [t:t].Delete

Patrick
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
matthewspatrick,

My mistake. T1 is a header, so it should be if all cells below T1 in column T are empty, then delete column T

Tosagua
Top Expert 2010

Commented:
OK, then:

    If Application.CountA(Range([t2], Cells(Rows.Count, "t"))) = 0 Then [t:t].Delete

Author

Commented:
matthewspatrick:

Column T is still there. This is what we are doing:

There is an IF statement in column T that compares two values, and if they are the same, the adjacent cell in column T is labeled "Add'l Delivery / Line".

Then we offer the user the option of deleting the duplicate entries ("Add'l Delivery / Line"). If they pick Yes, all rows with the value "Add'l Delivery / Line" are deleted. Then, of course there is no use for column T and we want to delete it.

If the user picks NO, the macro ends.  But in either case a Message Box pops up to instruct the user to save the file with a new name.

The following code is the end of the macro.

If MsgBox("Do You Want To Delete Duplicate Entries?", vbYesNo, "Title") = vbYes Then
Application.ScreenUpdating = False
Dim lastRow As Long
 
lastRow = Cells(65536, "T").End(xlUp).Row
 
For i = 1 To lastRow
    If (InStr(Trim(Cells(i, 20).Value), "Add'l Delivery / Line") > 0) Then
        Rows(i).Delete
        i = i - 1
        lastRow = lastRow - 1
    Else
    End If
    Debug.Print "i = " & i & "   lastRow = " & lastRow
    If i > lastRow Then 'Prevents infinite loop
        Exit For
    Else
    End If
Next
 
Else
    If Application.CountA(Range([t2], Cells(Rows.Count, "t"))) = 0 Then [t:t].Delete
    Range("A1").Select
    Application.Goto [A1]
    ScrollRow = 1
    ScrollColumn = 1
    Application.SendKeys "^{HOME}"      'CTRL + Home
    'Application.Goto Cells.SpecialCells(xlCellTypeVisible).Cells(1, 1)
    Application.ScreenUpdating = True
    MsgBox "The Macro Has Finished." & vbCr & "Save This File With A New Name."
 
End If
'
End Sub

Tosagua
Top Expert 2010

Commented:
Tosagua,

With respect, why did not disclose all of this **in the original question**?

By withholding relevant details, you (1) prevented yourself from getting a usable answer and (2) caused me to
waste my time, because I was trying to answer the question you actually asked instead of the question you
should have asked.

Please take this opportunity to restate your question, and this time include all relevant details, including just
what exactly will be in the cells in Col T.  For example, is it an IF() expression that returns either "Add'l Delivery / Line"
or a zero length string?

Finally, your For...Next loop should be rewritten as:

For i = lastRow To 1 Step -1
    If (InStr(Trim(Cells(i, 20).Value), "Add'l Delivery / Line") > 0) Then
        Rows(i).Delete
    Else
    End If
Next

Adjusting the counting variable for a For...Next loop is not a recommended practice.  In this instance, you avoid
that by counting backwards.

Patrick

Author

Commented:
matthewpatrick:

I apologize. I did realize all the complexities involved.

Column S has a carrier PRO number and Numerous Deliveries may be assigned to each PRO number.
So we  sort Column S in ascending order.
Then in cell T2 is the IF statement: IF((S2=S1),"Add'l Delivery / Line","").
This cell is copied down to the end of the data in Column S.
The result is that the 1st time a PRO number is listed in column S, it has a blank value in column T.
While all other listings of that PRO number have a  "Add'l Delivery / Line" value in column T
Then a copy - Paste Values locks the cell values in Column T

Some people need to see the duplicate entries (Add'l Delivery / Line) in column T.
However, the same dollar value is assigned to all entries under each PRO number.
At this point, the SUM values of any columns are greatly inflated.

So other people need to delete all rows with the value "Add'l Delivery / Line"  in column T.
This leaves column T empty, and we would like to just delete the empty column.

I greatly appreciate your assistance.

Tosagua
Top Expert 2010
Commented:
Tosagua,

OK, that makes things much clearer.  For my part, sorry for that outburst.

For the record, a cell with a formula in it is *not* empty.  In the future, please be careful not to refer to it as such.

This, then should work:

    If Application.CountIf(Range([t2], Cells(Rows.Count, "t")), "Add'l Delivery / Line") = 0 Then [t:t].Delete

Patrick

Author

Commented:
matthewspatrick,

It worked perfectly. We still need to add some more functions to the macro before it is finished, so there may be additional questions.

Actually, the situation is somewhat humerous. My boss dreams up involved, detailed, reports that need to come from people with limited computer expierence. So I need to create tools that meet the requirements and compensate for the limitations of the personnel. The last step is to translate the headers into Spanish, which is something else I do not know.

Thank you again for all of your advise and assitance.
Have a good weekend.

Tosagua
Top Expert 2010

Commented:
Tosagua,

Glad to help, and again, my apologies for getting cranky.  From your grading comment:

"Actually, the situation is somewhat humerous. My boss dreams up involved, detailed, reports that need to come
from people with limited computer expierence. So I need to create tools that meet the requirements and
compensate for the limitations of the personnel."

Sounds a lot like what I do all day :)

Patrick

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial