VBA code for blank columns

[SebastianHuang]

What is the vba code for
if column A and B are blank, then copy to cell A1
If column A and B are not blank.

Two separate codes.

Thank you a million!

 

[Joe4]

I don't think your question make sense.
I think we need more details.
If column A is blank, what exactly are you copying to cell A1?

Sometimes, showing a simple example of what you are trying to do goes a long way in explaining it.

 

[MARK858]

Why 2 codes, just use an If statement...

Sub testit()
    Range("D1").Value = "XXXX"
    Range("D1").Copy
    If WorksheetFunction.CountA(Range("A:B")) = 0 Then
        Range("A1").PasteSpecial ' or just .Paste if same sheet as this is
    Else
        Range("C1").PasteSpecial ' or just .Paste if same sheet as this is
    End If
    Application.CutCopyMode = False
End Sub

If you insist on 2 codes then for not blank it is

If WorksheetFunction.CountA(Range("A:B")) > 0 Then

Edit: Umm, I definitely need to find an easy way to see if someone else has posted on this new board

 

[SebastianHuang]

This is the actual code I currently have. So it pastes selection from active sheet to Test 2, and, go to cell a1, go all the way to right, move 1 over , then paste the values. The issue is that if the first two columns are empty, this would return error. So I need to set up an if so that if column A if empty, it will go to a1 and paste there. If column B is empty, then it will go to B1, and paste it there. Otherwise, it will go to A1, go to the right, move 1 over and paste it there. Thanks a million!!


Sub CopyPaste()
'
' CopyPaste Macro

Selection.EntireColumn.Select
Selection.Copy
Sheets("Test 2").Select
Application.Goto Reference:="R1C1"
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Application.Goto Reference:="R1C1"
ActiveSheet.Previous.Select
End Sub

 

[MARK858]

Not sure what you mean by

Otherwise, it will go to A1, go to the right, move 1 over and paste it there

if you mean the first column that is after the last column with data (in the below it is based on row 1, you need to tell us if that cell can ever be empty but data in other cells in the column) then try the code below.

Sub testit()
    Selection.EntireColumn.Copy
    If WorksheetFunction.CountA(Range("A:A")) = 0 Then
        Range("A1").PasteSpecial
    ElseIf WorksheetFunction.CountA(Range("B:B")) = 0 Then
        Range("B1").PasteSpecial
    Else
        Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).PasteSpecial
    End If
    Application.CutCopyMode = False
End Sub

 

[SebastianHuang]

I think I figured it out
Sub CopyPaste()
'
' CopyPaste Macro

Selection.EntireColumn.Select
Selection.Copy
Sheets("Test 2").Select
ActiveSheet.Select
If WorksheetFunction.CountA(Range("A:A")) = 0 Then
ActiveSheet.Select
Cells(1, 1).Select
ActiveSheet.Paste
ActiveSheet.Previous.Select

ElseIf WorksheetFunction.CountA(Range("B:B")) = 0 Then
ActiveSheet.Select
Cells(1, 2).Select
ActiveSheet.Paste
ActiveSheet.Previous.Select


Else

Application.Goto Reference:="R1C1"
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
ActiveSheet.Previous.Select
End If

End Sub