Find next empty row issue

Bungraman

Board Regular
Joined
May 26, 2010
Messages
126
I have this code that copies 4 cells of data and pastes them in the next available "blank" row. So for example: Row 3/ (col A) = 111, (B) =222, (C) 333 and (D) 444. Sometimes Colum A & B does not contain any values but C & D do, now when I run this code (below) it copies ok to the next empty row, but when Columns A & B are empty, it copies across ok, but in the next loop if A & B are present, it overwrites (pastes onto) the row that has no values/data in column A & B. It is as if the code sees the empty "A" cell and determines the entire row is empty when sometimes it is not.

Basically is this code looking at each row entirely to see if it is empty?

VBA Code:
Sub Copy_And_Arrange_Seperate_Sheet()

Dim i As Long

Dim copySheet As Worksheet
Dim pasteSheet As Worksheet

    
    Set copySheet = Worksheets("Sheet1")
    Set pasteSheet = Worksheets("Sheet2")
        copySheet.Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, 3)).Copy

    For i = 1 To 13
    pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    
    copySheet.Select
    ActiveCell.Offset(0, 4).Select
        copySheet.Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, 3)).Copy
    
    Next i
    

End Sub

Can anyone suggest a fix?
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,094
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
pasteSheet.Cells(ActiveSheet.UsedRange.Rows.Count, 1).Offset(1, 0).PasteSpecial xlPasteValues
 

Bungraman

Board Regular
Joined
May 26, 2010
Messages
126
What it needs to do is copy these cells to "Sheet2" . . . . .
capture1.JPG


And give this . . . . .

capture2.jpg


But what it is doing is recognizing the cells that have no values in column A & B as a empty row, and pasting newer values over the top. Such as . . . . .
capture3.jpg


Just thought pictures might explain it better. :)
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,227
Office Version
  1. 2013
Platform
  1. Windows
Not tested but maybe try...

VBA Code:
Sub Copy_And_Arrange_Seperate_Sheet()

Dim i As Long

Dim copySheet As Worksheet
Dim pasteSheet As Worksheet

    
    Set copySheet = Worksheets("Sheet1")
    Set pasteSheet = Worksheets("Sheet2")
    PstRow = 0
    For c = 1 To 4
    LstRow = pasteSheet.Cells(Rows.Count, c).End(xlUp).Row
    If LstRow > PstRow Then PstRow = LstRow
    Next c
        copySheet.Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, 3)).Copy

    For i = 1 To 13
    
    pasteSheet.Cells(i + PstRow, 1).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    
    copySheet.Select
    ActiveCell.Offset(0, 4).Select
        copySheet.Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, 3)).Copy
    
    Next i
    

End Sub
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,133,271
Messages
5,657,764
Members
418,411
Latest member
Excellency

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top