VBA If range [J:K] not empty, then copy [H:I] to the end of [J:K]

[Xiggie]

Hello!

I have two ranges, [H23:I32] and [J23:K50].

I need to copy values from [H23:I32] to [J23:K50] if [J23:K50] is empty, and if [J23:K50] is not empty I need to find the last row and add [H23:I32] below.
It's a growing list of values.

The "copy if empty" works, but the "add to the end of the list" doesn't unfortunately. It does something, but clearly not the thing I need.

Instead of copying H23-I23 to J:K row by row my code starts wiping data J23 > K23 > J24 > K24 one cell at a time instead of copying [H:I] to the end of [J:K]

Sub Total_Loop()

Application.ScreenUpdating = False

Dim c  As Range

For Each c In Range("J23:K50" & Cells(Rows.Count, "J").End(xlUp).Row)
If c.Value <> "" Then
Range("J23:K50" & Cells(Rows.Count, "J").End(xlUp).Row + 1) = Range("H23:I32")
Else: c.Value = c.Offset(, -2).Value
    
End If
Next
    
Application.ScreenUpdating = True

End Sub

Any suggestions how to fix this?

 

[gordsky]

Maybe something like this............. Change the worksheet = your worksheet name

Sub MoveData()
Dim lrow As Long
Dim ws As Worksheet
Set ws = Sheets(YourName)  'change here

If WorksheetFunction.CountA(ws.Range("J23:K50")) = 0 Then
     ws.Range("H23:I32").Copy ws.Range("J23")
Else
   lrow = ws.Range("J23:K50").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
   ws.Range("H23:I32").Copy ws.Range("J" & lrow + 1)
End If
End Sub

 

[Xiggie]

Thank you! Is there a way to do it without defining the worksheet?
Unfortunately it breaks the rest of my macros

 

[gordsky]

Thank you! Is there a way to do it without defining the worksheet?
Unfortunately it breaks the rest of my macros

You implied this is a standalon macro you required. If so defining the sheet has no bearing on your other macros.
You should always fully define ranges but if you want to do it without defining then try this but be aware it may not ive you the results you expect.

Sub MoveData()
Dim lrow As Long

If WorksheetFunction.CountA(Range("J23:K50")) = 0 Then
     Range("H23:I32").Copy Range("J23")
Else
   lrow = Range("J23:K50").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
   Range("H23:I32").Copy Range("J" & lrow + 1)
End If
End Sub

 

[Xiggie]

Thank you! Is there a way to do it without defining the worksheet?
Unfortunately it breaks the rest of my macros

Nevermind! It was all me, I copied it wrong! But I am getting weird results:

It is kind of doing what I want it to do but it copies [F:G] instead of [H:I] to [J:K].
But at least it's alive!

 

[gordsky]

Nevermind! It was all me, I copied it wrong! But I am getting weird results:

It is kind of doing what I want it to do but it copies [F:G] instead of [H:I] to [J:K].
But at least it's alive!

it would be impossible for that code to copy F:G. Have you named any ranges etc. If this is part of a bigger code then maybe paste the entire code here.

 

[Xiggie]

Unfortunately it doesn't fit, so I uploaded my excel to file.io https://www.file.io/zXX8/download/ovpChH1BJIsV

However, I think I understand the nature of my issue: it copies formulas, not values. I think I know a workaround for it!

 

[gordsky]

Unfortunately it doesn't fit, so I uploaded my excel to file.io https://www.file.io/zXX8/download/ovpChH1BJIsV

However, I think I understand the nature of my issue: it copies formulas, not values. I think I know a workaround for it!

if you want it to copy values then use this code. As you did not specify you wanted values in your post the code wasnt written as such

Sub MoveData()
Dim lrow As Long
Dim ws As Worksheet
Set ws = Sheets(YourName)  'change here


If WorksheetFunction.CountA(ws.Range("J23:K50")) = 0 Then
     ws.Range("H23:I32").Copy
     ws.Range("J23").PasteSpecial xlPasteValues
Else
     lrow = ws.Range("J23:K50").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
     ws.Range("H23:I32").Copy
     ws.Range("J" & lrow + 1).PasteSpecial xlPasteValues
End If
End Sub

 

[Xiggie]

And now it works!! Splendid and thank you so very much!!