VBA: IF conditions not being recognized

Question

Want to do:

A.If only one row is present in the data sheet, copy and paste that lone row and paste it to the named sheet

B.if there are multiple rows of data, copy all then paste

Issues Having with Current Code: it disregards the first if condition and goes straight to the next one which copies the range and everything below even if theres only one row of data present.

here's my code with the following condtions:

ws2 = source data sheet

wsA = sheet data will be pasted on

copied data if conditions are met should be pasted on the last available blank row in column A of WsA

k = ws2.Range("a6", ws2.Range("a6").End(xlDown)).Rows.Count

If k <= 1 Then

    ws2.Activate
    rngB.Select
    Selection.Copy
    wb2.Activate
    wsA.Activate
    Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

Else
    ws2.Activate
    rngB.Select
    Range(rngB, ActiveCell.End(xlDown)).Select
    Selection.Copy

    wb2.Activate
    wsA.Activate
    Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False


End If

data sheet

Many things here, first and most important: Read [this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) on how to avoid using select. Second thing, k is defined from Row 6, row 6 is a header? is where the data would start? If the later it will go down to the last row of the sheet when there is only 1 row of data. Can you show a screen from the data worksheet? — Damian, Mar 18 '19 at 10:06
i added a screen cap of the data sheet row 6 is where the data start — alfaromeo30, Mar 18 '19 at 10:33

score 0 · Accepted Answer · answered Mar 18 '19 at 10:15

0

If there is no data below row 6 then ws2.Range("a6").End(xlDown) will extend down to the bottom of the sheet (so k > ~1000000)

To detect if only one row of data exists, try

If IsEmpty(ws2.Range("a6").Offset(1,0) then
    ' Only one row
Else
    ' More than one row
End If

And, head the advise to avoid select.

answered Mar 18 '19 at 10:15

chris neilsen

52,446
10
84
123

thanks for the advise. im super new writing code. i just stitch codes from both the recorder and the ones i find in forums like this. a friend also suggested that i use WITH instead of SELECT. will two WITH's work? there's still more code on top of what i added here. – alfaromeo30 Mar 18 '19 at 11:53

VBA: IF conditions not being recognized

1 Answers1