CODEWITHSUNDEEP
vbaexcel
Holene
Holene

Reputation: 573

Copy values from multiple columns in same row VBA

I'm a VBA noob with a massive table of analysis results where each row is containing results from a different date. I'm looking for values in certain columns, and they are some times empty. When they are, I'm not interested in them. The values I want are supposed to be copied to a new sheet to be collected in a smaller table.

I have written a script where I loop through the rows in the masterTable, and I am able to identify the rows with the values I'm interested in. However, I am not able to copy the value from the different cells in the identified row to a new sheet.

I've tried using Union to make a range inlcuding the columns that are relevant for copying. 

Dim searchCells As Range
Dim masterTable As Range

Set searchCells = Union(Columns("R"), Columns("S"), Columns("T"), Columns("X"), Columns("Z"), Columns("AF"), Columns("AQ"), Columns("AT"), Columns("AY"), Columns("AV"), Columns("BB"), Columns("BD"), Columns("BG"))
Set masterTable = Worksheets("Sheet0").Range("A3:BG2022")

a = 1
For i = 1 To masterTable.Rows.Count
  If Application.WorksheetFunction.CountA(searchCells(i).Value) <> 0 Then ' look for values among the relevant columns in row(i).
    Debug.Print "Found data at "; i
    Worksheets("Sheet0").searchCells.Rows(i).Copy ' copy data from searchCells
    Worksheets("Results").Range("C1").Offset(a, 0).paste ' paste data to destination
    a = a + 1 ' increment destination row offset
  End If
Next

My idea of the searchCells are not working, as I "find data" in all rows, and I'm not able to run the .Copy and the .Paste methods. All help is appreaciated!

EDIT: upon compilation VBA throws the following error on the copy-line:

Run-time error '438':

Object doesn't support this property or method

Upvotes: 1

Views: 3209

Answers (1)

Rory
Rory

Reputation: 34045

To copy the relevant rows (for those columns only) where there is data in at least one cell, you could use:

Dim searchCells           As Range
Dim masterTable           As Range
Dim rRow                  As Range

Set searchCells = Range("R:T,X:X,Z:Z,AF:Af,AQ:Aq,AT:AT,AV:AV,AY:AY,BB:BB,BD:BD,BG:BG")
Set masterTable = Worksheets("Sheet0").Range("A3:BG2022")

a = 1
For i = 1 To masterTable.Rows.Count
    Set rRow = Intersect(searchCells, searchCells.Cells(i, 1).EntireRow)
    If Application.WorksheetFunction.CountA(rRow) <> 0 Then    ' look for values among the relevant columns in row(i).
        Debug.Print "Found data at "; i
        rRow.Copy Worksheets("Results").Range("C1").offset(a, 0)    ' paste data to destination
        a = a + 1    ' increment destination row offset
    End If
Next

Upvotes: 2

Related Questions