VBA Copy Destination:= Finding First Empty cell/row

[beartooth91]

Sub First_Empty_Row()
Worksheets("Combined").Range("B" & Rows.Count).End(xlUp).Select
End Sub

The above finds my last cell in column B with data.
Is there a way to write this, on one line and without using variables, to find the first empty cell/row?
What I'm trying to do here is copy and paste a bunch of rows from other workbooks and I'd like to use the .Copy Destination:= verbiage to do it but I need the first empty cell.
Yep, I need the .Row + 1 ending in but can't find a way to work it in that line......

 

[Joe4]

Try something like:

.Copy Destination:=Worksheets("Combined").Range("B" & Rows.Count).End(xlUp).Offset(1, 0)

 

[beartooth91]

Try something like:

.Copy Destination:=Worksheets("Combined").Range("B" & Rows.Count).End(xlUp).Offset(1, 0)

Not quite.... When I run it; it pastes at the next (ie. 2nd) blank cell below the data. (Leaving a blank row between the imported data sets.)

 

[Joe4]

Can you show me a screen print of the range you are trying to post to, so I can see exactly what it looks like?
And where is the data you are copying from?
Can you show me a screen print of that, as well as your entire VBA code block?

I want to try to re-create your scenario on my side so I can test it out for myself thoroughly.

 

[MARK858]

@beartooth91 what does the message box below return?

Sub test()
MsgBox AscW(Worksheets("Combined").Range("B" & Rows.Count).End(xlUp)) & " " & Len(Worksheets("Combined").Range("B" & Rows.Count).End(xlUp))
End Sub

 

[beartooth91]

Can you show me a screen print of the range you are trying to post to, so I can see exactly what it looks like?
And where is the data you are copying from?
Can you show me a screen print of that, as well as your entire VBA code block?

I want to try to re-create your scenario on my side so I can test it out for myself thoroughly.

My apologies.... Your code works. One of my source worksheets had a blank row at the top of the data.

 

[MARK858]

You need to mark post 2 as the solution, not mine

 

[beartooth91]

My only follow up question is there a way to write this same line, to Select......for preview/troubleshooting purposes?

 

[Joe4]

My only follow up question is there a way to write this same line, to Select......for preview/troubleshooting purposes?

In that case, I would find the row first, and then you can reference it multiple times, i.e.

Dim r as Long
r = Worksheets("Combined").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Row

So then you could use this to copy it:

.Destination:=Worksheets("Combined").Range("B" & r)

and

Worksheets("Combined").Range("B" & r).Select

to select it.

Note that if you are on a different sheet while this code is running, you may need to explicitly select the sheet first in order to select that cell, i.e.

Worksheets("Combined").Select
Range("B" & r).Select