Finding the second empty row

[Cloud67]

Hi,

I have a code to copy and paste values till first empty row but then I want vba to copy and paste values after that till it find second empty row. So For first I did

 Lastrow = Cells.Find(What:="*", After:=[A20], SearchOrder:=xlByRows).Row
Range("A1:H" & Lastrow - 1).Offset(19, 0).Copy
ws2.Activate
Range("A22").Insert Shift:=xlDown

and for the second I did

Nextrow = 21 + Lastrow
Lastrow2 = Cells.Find(What:="*", After:=ws1.Cells(Nextrow, 1), SearchOrder:=xlByRows).Row
Range("A1:H" & Lastrow2 - 1).Offset(Nextrow, 0).Copy
ws2.Activate
Range("A" & Nextrow).Insert Shift:=xlDown

For first one it works but for second one it is not finding the empty row after and instead putting the "Lastrow2" value same as "Nextrow". Can someone help me with that. Thanks

 

[DanteAmor]

You can explain what you need to do, but not with code.
I understand, you want to copy something and paste somewhere.
Explain what you want to copy and where you want to paste it. (explanation without using code)
Is this in a loop?

 

[Cloud67]

It's not a loop. I have two tables on worksheet (separated by empty row) and I want to copy and paste them to another worksheet (template). I was able to copy and paste first one but couldn't do it for second one. There is data after the second table too but I don't want that. So I just want vba to find second empty row and stop there.

 

[DanteAmor]

Do you know in which row the first table starts?
The first table always has 19 rows?

Do you have something like this?
Some relevant information that you can share or a pattern to consider.


Or you can upload an image of how you have your data to give me an idea.

 

[Cloud67]

So the first table always start at A19 but table2 starting point is not fix because number of rows in Table1 are not fixed. So the worksheet is like

data
data
(Empty Row)
Table1
(Empty Row)
table2
(Empty Row)
Data
Data

Sorry i don't know how to insert the image

 

[DanteAmor]

With that information is enough.


Just missing comment on where you want the paste. I guess the first table goes on A22 and the second table goes down on the first one.

Try this:

Sub copy_data()
    'Finding the second empty row
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim r As Range, a As Range, n As Long
    
    Set ws1 = Sheets("Sheet1")
    Set ws2 = Sheets("Sheet2")
    Set r = ws1.Range("A19", ws1.Range("A" & Rows.Count).End(xlUp)(2))
    r.SpecialCells(xlCellTypeConstants).Areas(1).Copy   [COLOR=#0000ff]'copy first table[/COLOR]
    n = r.SpecialCells(xlCellTypeConstants).Areas(1).Rows.Count
    ws2.Range("A22").Insert Shift:=xlDown
    r.SpecialCells(xlCellTypeConstants).Areas(2).Copy   [COLOR=#008000]'copy second table[/COLOR]
    ws2.Range("A" & 22 + n).Insert Shift:=xlDown
End Sub

If you want the second table to have a separation row after the first table then use this:

    ws2.Range("A" & 22 + n [COLOR=#ff0000]+ 1[/COLOR]).Insert Shift:=xlDown

 

[Cloud67]

r.SpecialCells(xlCellTypeConstants).Areas(2).Copy

Above row is giving object defined error.

 

[DanteAmor]

r.SpecialCells(xlCellTypeConstants).Areas(2).Copy

Above row is giving object defined error.

Then there is no empty row between the 2 tables.
You must do the test according to the data you mentioned.

data
data
(Empty Row)
Table1
(Empty Row)
table2
(Empty Row)
Data
Data

 

[Cloud67]

Hi Dante, I do have empty rows. My first code was able to recognize the empty row after the first table but your code couldn't recognize any

[COLOR=#333333]r.SpecialCells(xlCellTypeConstants).Areas(1).Copy[/COLOR]

Above code line copy and pasted the first table then second table and then data after that. So there was nothing left for other code to select.

 

[DanteAmor]

My intention is only to try to help you.
I append an update to the code to copy from column A to H.
I also attach my test file for you to review.

The code is based on your comment:

So the first table always start at A19

Let me know if you have any questions.

Sub copy_data()
    'Finding the second empty row
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim r As Range, a As Range, n As Long
    
    Set ws1 = Sheets("Sheet1")
    Set ws2 = Sheets("Sheet2")
    Set r = ws1.Range("A19", ws1.Range("A" & Rows.Count).End(xlUp)(2))
    n = r.SpecialCells(xlCellTypeConstants).Areas(1).Rows.Count
    r.SpecialCells(xlCellTypeConstants).Areas(1).Resize(n, 8).Copy  'copy first table
    ws2.Range("A22").Insert Shift:=xlDown
    n = r.SpecialCells(xlCellTypeConstants).Areas(1).Rows.Count
    r.SpecialCells(xlCellTypeConstants).Areas(2).Resize(n, 8).Copy  'copy second table
    ws2.Range("A" & 22 + n + 1).Insert Shift:=xlDown
End Sub

My file:
https://www.dropbox.com/s/jrrg5fdb1um6vrt/copydata.xlsm?dl=0