Excel VBA - Column count using variants

Question

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

OpiesDad · Accepted Answer · 2015-03-02 16:30:34Z

1

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.

answered Mar 2, 2015 at 16:30

OpiesDad

3,4352 gold badges19 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

stewart jordan Over a year ago

Thanks very much for this, it works perfectly in my code. I'm teaching myself as I go along (hence the issues!) and had some rondebruin code which worked for the row count which I was trying to change to count columns but with no luck.

Collectives™ on Stack Overflow

Excel VBA - Column count using variants

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related