VBA - copy cell if it is not empty

[Kristina96]

Hi everyone,

I have the following problem:
I have a small table with 2 columns (E and F):
I want to copy all values from column F into column E but only if there is a value in column F. I cannot use the "normal" copy function as that would copy empty cells. There are values in column E that are supposed to be kept if there is no value in column F.
All of this should be done with VBA as it is part of a macro.
I am very thankful for every idea or help!
Thank you and regards
Kristina

 

[vcoolio]

Hello Kristina,

As a starting point, see if the following does the task for you:-

Sub Test()

    Dim c As Range
    Dim lr As Long: lr = Sheet1.Range("E" & Rows.Count).End(xlUp).Row

For Each c In Range("F1:F" & lr)
      If Not IsEmpty(c) Then
      c.Offset(, -1).Value = c.Value
      End If
Next c

End Sub

Test it separately in a copy of your workbook first.

I hope that this helps.

Cheerio,
vcoolio.

 

[Akuini]

Another way:

    With Range("F1", Cells(Rows.count, "F").End(xlUp))
    .Copy
    .Offset(, -1).PasteSpecial paste:=xlPasteValues, [COLOR=#0000ff]SkipBlanks:=True[/COLOR]
    End With

 

[Kristina96]

Thank you for your help!
I tried this because I only want to look as rows 31 to 34 in the columns:

Dim c As Range
Dim lr As Long: lr = Worksheets("Sheet1").Range("E31:E34" & Rows.Count).End(xlUp).Row


For Each c In Range("F31:F34" & lr)
      If Not IsEmpty(c) Then
      c.Offset(, -1).Value = c.Value
      End If
Next c

I get an error in the second line when running the macro. Do you know what could be the problem?

 

[Kristina96]

@Akuini: Is it possible to adapt that code in a way to only look at rowas31-34. That would be amazing. Thank you!

 

[Joe4]

You forgot to remove the row number 34 after the E (Rows.Count returns the maximum row number, so you want to remove the row number reference of the range you are attaching that to).
So change it here:

Dim lr As Long: lr = Worksheets("Sheet1").Range([COLOR=#ff0000]"E31:E" & Rows.Count[/COLOR]).End(xlUp).Row

and here:

For Each c In Range([COLOR=#ff0000]"F31:F" & lr[/COLOR])

 

[Kristina96]

I don't get an error anymore but it is not copying the cells. There are more rows in columns E and F below that should remain unchanged aswell. How can I solve this?
Would it be easier to change akuinis code and let it stop in row 34?

 

[Joe4]

OK, I was only commenting on the issue with your code, but did not analzye it or the original question.
If you just want to focus on a specific range, you should not need to find the last row.

So, does this simplified version do what you want?

Dim c As Range

For Each c In Range("F31:F34")
      If Not IsEmpty(c) Then
      c.Offset(, -1).Value = c.Value
      End If
Next c

 

[Akuini]

Try:

    With Range("F31:F34")
    .Copy
    .Offset(, -1).PasteSpecial paste:=xlPasteValues, SkipBlanks:=True
    End With

 

[Kristina96]

That makes sense but it is still not copying the values. The macro is running without error but nothing changes...