CODEWITHSUNDEEP
vbaexcel
Raul Gonzales
Raul Gonzales

Reputation: 906

Selecting an entire row but the first column in VBA

I have the below code and what loop does is "copies" all the data from 1 row on worksheet1 and "pastes" it on worksheet2. the problem that I have is that I need the row to be pasted in worksheet2 but starting from column B and not column A.

Do While rowCounter < 2200
    If Cells(rowCounter, colCounter).Value <> "AP Statistics Summary" Then
        ThisWorkbook.Sheets("Throughput Per AP").Rows(throughputAP).Offset(1, 0).Value = ThisWorkbook.ActiveSheet.Rows(rowCounter).Value
        throughputAP = throughputAP + 1
    End If
    rowCounter = rowCounter + 1
Loop

the issue happens on this line:

ThisWorkbook.Sheets("worksheet2").Rows(throughputAP).Offset(1, 0).Value = ThisWorkbook.ActiveSheet.Rows(rowCounter).Value

so the code runs ok but the data gets copied to worksheet2 starting from column A and I need it to start from column B

help please!

Upvotes: 0

Views: 1140

Answers (2)

Shai Rado
Shai Rado

Reputation: 33672

Try the code below, it will copy the EntireRow and uses PasteSpecial xlValues to paste only the values.

Do While rowCounter < 2200
    If Cells(rowCounter, colCounter).Value <> "AP Statistics Summary" Then
        ThisWorkbook.ActiveSheet.Rows(rowCounter).Copy
        ThisWorkbook.Sheets("Throughput Per AP").Rows(throughputAP).Offset(1, 0).PasteSpecial xlValues
        throughputAP = throughputAP + 1
    End If
    rowCounter = rowCounter + 1
Loop

However, in your case, I would replace your Do While with For rowCounter.

Also, use fully qualified Worksheets, and not ActiveSheet.

Version 2 of your code

For rowCounter = 2 To 2200 ' <-- start from 2 (or your start row)
    With Worksheets("YourSheetName") ' <-- put here you sheet's name instead of ActiveSheet
        If .Cells(rowCounter, colCounter).Value <> "AP Statistics Summary" Then
            .Rows(rowCounter).Copy
            ThisWorkbook.Sheets("Throughput Per AP").Rows(throughputAP).Offset(1, 0).PasteSpecial xlValues
            throughputAP = throughputAP + 1
        End If
    End With
Next rowCounter

Upvotes: 0

YowE3K
YowE3K

Reputation: 23974

You should be able to resize the source row to be one column less wide than the full row, then copy that to column A (i.e. copying B:XFD to A:XFC):

'Using a With simply to shorten the code
With ThisWorkbook.ActiveSheet.Rows(rowCounter)
    ThisWorkbook.Sheets("worksheet2").Rows(throughputAP + 1).Resize(1, .Columns.Count - 1).Value = _
                  .Resize(1, .Columns.Count - 1).Offset(0, 1).Value
End With

Or, if you are trying to copy A:XFC to B:XFD you can do

'Using a With simply to shorten the code
With ThisWorkbook.ActiveSheet.Rows(rowCounter)
    ThisWorkbook.Sheets("worksheet2").Rows(throughputAP + 1).Resize(1, .Columns.Count - 1).Offset(0, 1).Value = _
                  .Resize(1, .Columns.Count - 1).Value
End With

Or, if you are happy to hardcode the column letters, you can use

ThisWorkbook.Sheets("worksheet2").Rows(throughputAP + 1).Range("A1:XFC1").Value = _
                  ThisWorkbook.ActiveSheet.Rows(rowCounter).Range("B1:XFD1").Value

or switching the "A1:XFC1" and "B1:XFD1" if you want to go the opposite direction

(Those bits of code replacing the line you are having problems with.)

Upvotes: 2

Related Questions