VBA - Last row not working when there is one row of data

[xyacc]

Hello,

I need to create a code that will copy the data to the last cell and paste to another workbook in one sheet. The code I have work well when there is two or more rows of data and it does not work when there is only one row of data in worksheet. How do I write a code that will not break when there is a sheet with one row of data?

Thank you in advance!!

Dim M As Long
Dim LastRow As Long
Dim CopyCell As Variant
Dim CopyR As Long
Dim SearchR As Range
Dim wb As Workbook

Set wb = ActiveWorkbook

wb.Activate
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Workbook("Book 1.xlms").Activate
Sheets(M + 1).Activate
Set SearchR = Range("A:A").Find(What:="*", LookIn:=xlValues, lookat:=xlWhole)
Set CopyR = SearchR.Offset(3, 0)
Range(CopyR, CopyR.End(xlDown).End(xlToRight)).Copy
Cells(LastRow + 1, 1).Select
ActiveCell.PasteSpecial (xlPasteValues)

 

[offthelip]

You can avoid the error by changing this line:

Set SearchR = Range("A:A").Find(What:="*", LookIn:=xlValues, lookat:=xlWhole)

to

lastrow1 = Cells(Rows.Count, 1).End(xlUp).Row
Set SearchR = Range(Cells(lastrow1, 1), Cells(lastrow1, 1))

however looking at your code you seem to be copying blank rows, so I am not sure what you are trying to do.

 

[xyacc]

You can avoid the error by changing this line:

Set SearchR = Range("A:A").Find(What:="*", LookIn:=xlValues, lookat:=xlWhole)

to

lastrow1 = Cells(Rows.Count, 1).End(xlUp).Row
Set SearchR = Range(Cells(lastrow1, 1), Cells(lastrow1, 1))

however looking at your code you seem to be copying blank rows, so I am not sure what you are trying to do.

Here is the full code that I created, I have ran this macro and it will break when the sheet has only single row of data. I have search and tried multiple approaches but it doesn't works. Can you please help me to amend the code below?

Sub Copy()

Dim i As Long
Dim j As Long
Dim k As Long
Dim LRow As Long
Dim CCell As Variant
Dim CRange As Range
Dim SRange As Range
Dim wb As Workbook

Set wb = ActiveWorkbook

i = wb.Sheets("Sheet1").Range("K2").Values  'The first sheet to copy
j = wb.Sheets("Sheet1").Range("K3").Values 'The first sheet to copy

Range(Range("A2"), Range("A2").End(xlDown).End(xlToRight)).ClearContents

For k = i To j

    wb.Activate
    LRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    Workbooks("Result Opening Macro.xlsm").Activate
    Sheets(k + 1).Activate
    
    Set SRange = Range("B:B").Find(what:="SPCODE", LookIn:=xlValues, lookat:=xlWhole)
    Set CRange = SRange.Offset(RowOffset:=2, ColumnOffset:=0)
    Range(CRange, CRange.End(xlDown).End(xlToRight)).Copy
    wb.Activate
    Cells(LRow + 1, 1).Select
    ActiveCell.PasteSpecial (xlPasteValues)
    
Next k
    
Workbooks("Result Opening Macro.xlsm").Sheets("Master").Activate
wb.Activate
    
End Sub

 

[offthelip]

You can test if the range is set like this:

Set Srange = Range("B:B").Find(what:="SPCODE", LookIn:=xlValues, lookat:=xlWhole)
If Srange Is Nothing Then
MsgBox ("Srange is nothing")
Else
MsgBox ("Srange is set")
End If

 

[xyacc]

I tried but it only show the pop up box with the info instead solving the issues currently. Do you have any approaches? Thank you.

 

[Alex Blakenburg]

Can you give us an XL2BB on a sheet with 1 row that it fails on and one with more rows that it works on.

Also the code you have provided can't be your working code.
These 2 lines are invalid. It needs to be Value not Values for it to work.

i = wb.Sheets("Sheet1").Range("K2").Values  'The first sheet to copy
j = wb.Sheets("Sheet1").Range("K3").Values 'The first sheet to copy

I have some concerns around using the format below and it used twice in the code.
a) if you are on the last row or if there is only 1 row it will go right to the bottom.
b) it uses the last row to work out how many columns there are. Usually you work out how many columns using the header row.

 Range(CRange, CRange.End(xlDown).End(xlToRight)).

 

[MARK858]

@xyacc Try changing

Set SRange = Range("B:B").Find(what:="SPCODE", LookIn:=xlValues, lookat:=xlWhole)

to

    With Range("B:B")
        Set SRange = .Find(what:="SPCODE", After:=.Cells(.Cells.Count), LookIn:=xlValues, lookat:=xlWhole)
    End With