Determine in VBA if range (table column) is empty

  • Hi, I want the macro to check if a column in a defined table has values and, in this case, copy it and overwrite another column of the same table.


    I have developed a solution, which is apparently correct (no errors appear) but runs the macro regardles if it has values or not.


    Code
    1. Sub CopyPasteIfEmpty()
    2. Sheets("Sheet 1").Activate
    3. If Not IsEmpty("Sheet 1[Column to be copied]") Then
    4. Range("Sheet 1[Column to be copied]").Copy[INDENT]Range("Sheet 1[Column to be overwritten]").Select
    5. Selection.PasteSpecial Paste:=xlPasteValues, Operation:?xlNone, SkipBlanks:=False, Transpose:=False
    6. Range("Sheet 1[Column to be copied]".ClearContents[/INDENT]
    7. End if


    The thing is that this is part of a bigger macro which i run to clean the excel from the data of the last time i used it (to audit). So if the column "Column to be copied" has no values, the macro would overwrite the values of the "Column to be overwritten" column with no values (wich is annoying to say the least).


    Thanks in advance, and excuse me if i made any mistakes, as i am new to this forum.

  • Re: Determine in VBA if range (table column) is empty


    Code
    1. Sub test()
    2. If WorksheetFunction.CountA(Range("A1:D7")) = 0 Then
    3. MsgBox "Range is empty!"
    4. Else
    5. MsgBox "Range is not empty!"
    6. End If
    7. End Sub
  • Re: Determine in VBA if range (table column) is empty


    Thanks patel, but its still not working for table defined ranges (at least not for mine). I tried it defining the range as you recommendated, and it works, but if i use the table range (more dinamic, cause the tables vary in number of rows from audit to audit) it always considers that the range is not empty, even if it is.


    As i said, i modified it to this, but its still not working correctly (it runs the macro anyway):

    Code
    1. Sub test()
    2. If WorksheetFunction.CountA("Sheet 1[Column to be copied]") = 0 Then
    3. MsgBox "Range is empty!"
    4. Else
    5. MsgBox "Range is not empty!"
    6. End If
    7. End Sub
  • Re: Determine in VBA if range (table column) is empty


    OK my bad!! i forgot to put "Range"!! now it works perfectly like this:

    Code
    1. Sub test()
    2. If WorksheetFunction.CountA(Range("Sheet 1[Column to be copied]")) = 0 Then
    3. MsgBox "Range is empty!"
    4. Else
    5. MsgBox "Range is not empty!"
    6. End If
    7. End Sub


    Thank you very much! Regards!