Cell Remains Blank When Copying to Other Worksheet

Question

I'm copying data from Worksheet1 & Worksheet2 into Worksheet3.

The code goes through the desired range in Worksheet 1, finds an X and copies the value of the column 9 rows to the right of the X to Worksheet3. Then it does the same on Worksheet2.

Dim WrkSht As Worksheet
Dim WrkShtCol As Sheets
Dim cl As range
Dim rw As range

Sub CopyFromSheetsToOtherSheet()

Set WrkShtCol = Worksheets(Array("Worksheet1", "Worksheet"))

For Each WrkSht In WrkShtCol

    For Each rw In WrkSht.range(Antwortrange).Rows   'Reihen durchlaufen innerhalb der Antwortrange
        For Each cl In rw.Cells
            If LCase(cl.Value) = "x" Then
                cl.Offset(0, 9).Copy Sheets("Worksheet3").range("A" & Rows.Count).End(xlUp).Offset(1)       'Jede Zelle mit Value "x" 9 Spalten nach rechts auswählen (Handlungsempfehlung), weitergeben
            End If
        Next cl
    Next rw

The code starts copying into Worksheet3 starting at Cell A2, then A3, A4 and so on.
I want it to start at A1. Why doesn't it copy to A1 as the first step?

I'm later using functions to find blanks and therefore I can't have a blank in Cell A1.

Answer 1

I'm sure there is a cleaner way to do this but first thing to that came to mind is to just add some conditional checks on your last row calculation (lr)

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Worksheet3")

'For Each cl...

    lr = ws.Range("A" & ws.Rows.Count).End(xlUp).Offset(1).Row
    If lr = 2 And ws.Range("A1") = "" Then lr = 1
    cl.Offset(0, 9).Copy ws.Range("A" & lr)

'Next cl...

---

When using Offset(1) in this scenario you will end up with lr = 2 two times. First when the column is blank and again when only A1 is used. To resolve you can just add a conditional statement when lr = 2 to determine which range to go with