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
    Sub CopyPasteIfEmpty()
    Sheets("Sheet 1").Activate
    If Not IsEmpty("Sheet 1[Column to be copied]") Then
    Range("Sheet 1[Column to be copied]").Copy[INDENT]Range("Sheet 1[Column to be overwritten]").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:?xlNone, SkipBlanks:=False, Transpose:=False
    Range("Sheet 1[Column to be copied]".ClearContents[/INDENT]
    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
    Sub test()
      If WorksheetFunction.CountA(Range("A1:D7")) = 0 Then
        MsgBox "Range is empty!"
      Else
        MsgBox "Range is not empty!"
      End If
    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
    Sub test() 
        If WorksheetFunction.CountA("Sheet 1[Column to be copied]") = 0 Then
            MsgBox "Range is empty!" 
        Else
            MsgBox "Range is not empty!" 
        End If
    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
    Sub test() 
        If WorksheetFunction.CountA(Range("Sheet 1[Column to be copied]")) = 0 Then 
            MsgBox "Range is empty!" 
        Else 
            MsgBox "Range is not empty!" 
        End If 
    End Sub


    Thank you very much! Regards!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!