Delete column if the column only contains text

[Luin29]

Hi Everyone,

I would like to ask for some help writing a few lines of codes to speed up the generation of a few reports. I would like to have the code delete a column if the column only contains text.

So far I have the following code I have created through a bit of research online

 Dim addcell As Range
 Dim lrow As Long, lcol As Long
 Dim WorkRng As Range
 lrow = Cells(Rows.Count, 1).End(xlUp).Row
 lcol = Cells(1, Columns.Count).End(xlToLeft).Column
 Range("A1").End(xlDown).Offset(1).EntireRow.NumberFormat = "General"
 Range("A1").End(xlDown).Offset(, 32).Select
 Set addcell = ActiveCell
 Range("A1").End(xlDown).Offset(1, 32).Formula = "=COUNT(CR[-1]:addcell)"
 Range("A1").End(xlDown).Offset(1, 32).Copy
 Range(Range("A1").End(xlDown).Offset(1, 32), Cells(lrow + 1, lcol)).PasteSpecial xlPasteFormulas
 Range(Range("A1").End(xlDown).Offset(1, 32), Cells(lrow + 1, lcol)).Copy
 Range(Range("A1").End(xlDown).Offset(1, 32), Cells(lrow + 1, lcol)).PasteSpecial xlPasteValuesAndNumberFormats
 Range("A1").End(xlDown).Offset(1).Delete Shift:=xlToLeft
 Set WorkRng = Range(Cells(lrow + 1, 31), Cells(lrow + 1, lcol))
 For Each cell In WorkRng
    If cell.Value < 0 Then cell.EntireColumn.Delete
        Next
 End Sub

Thank you

 

[Kaper]

I've not analyzed code in nretail, but first source of potential problem I spotted is <, while it shall be rather = or, <=, because if there is no numbers in given column, formula result will be 0:
If cell.Value = 0 Then cell.EntireColumn.Delete

 

[Luin29]

Thanks Kaper. That is a problem that I missed. My main problem is applying the count function through vba to determine if there is a number in the given column. As the number of rows will differ from the one report to the next, I would like to the range that will be analyzed to be dynamic but have been unsuccessful establishing this so far.

 

[Kaper]

OK,
I got used to have a chance to work with sample data (as it is on my "home forum": excelforum.pl or international: excelforum.com). But let's try to see what you try to achieve here:

Set addcell = ActiveCell
 Range("A1").End(xlDown).Offset(1, 32).Formula = "=COUNT(CR[-1]:addcell)"

You set range variable to active cell, but then try to use it in the formula. Does it work? I do not think so.
you are mixing r1c1 addressing with just a string (nor value neither address "addcell"
I'd write this code line as:

Range("A1").End(xlDown).Offset(1, 32).Formular1c1 = "=COUNT(R[-1]C:" & addcell.address(,,[COLOR=#333333][FONT=&quot]xlR1C1)[/FONT][/COLOR] & ")"

 

[Luin29]

Thanks Kaper. Your explanation helped out greatly.