CODEWITHSUNDEEP
vbaloopsexcelcountvariants
stewart jordan
stewart jordan

Reputation: 13

Excel VBA - Column count using variants

I have searched the forums but I am really struggling to get part of my code to work. Basically the idea is to search sheet 1 and copy one or more columns depending on the criteria to a specific worksheet. i.e. if sheet 1 columns 1 and 3 contain "copy 01" then copy both columns to a sheet 2 and if sheet 1 columns 2 and 4 contain "copy 02" then copy both columns to a sheet 3 etc.

I can count rows fine using the code, but can't count columns. Seems to relate to not fiding the column range but I have no ideas to fix this! Any help would be much appreciated.

 'Row    
Dim NR As Long
Dim d As Variant

d = ws1.Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).Value
   For NR = 1 To UBound(d, 1)

'column
Dim NC As Long
Dim e As Variant

e = ws1.Range(Cells(1, Columns.Count).End(xlToLeft).Column).Value 
    For NC = 1 To UBound(e, 1)

Thanks, Stewart

Upvotes: 1

Views: 2283

Answers (1)

OpiesDad
OpiesDad

Reputation: 3435

You want this:

e = range("A1:" & split(cells(1,cells(1,columns.Count).end(xlToLeft).column).address(true,false), "$")(0) & "1").Address

The cells(1, columns.count).end(xlToLeft).column) gets the last column number (for example 13 for 'M').

Putting this into cells(1, lastcolNum) gets a cell that represents the cell in the first row of this column (for example Cell M1).

The address(true, false) method gets the cell reference with a dollar sign before the row but not before the column letter (for example "M$1"

The split function returns an array which splits the input string by the "$" character (for example array - ("M","1")

The (0) returns the 0th element in the returned array (for example "M") Then putting this into the range function returns the range (for example) "A1:M1"

I'm not entirely sure what you're trying to do with the UBound function here. It would make more sense to make 

e = cells(1,columns.count).end(xlToLeft).column

and then loop through

For N = 1 To e

As this will loop through each column.

Upvotes: 1

Related Questions