Passing the Used Range to a range object...

[Adaytay]

Hi folks, quick query on UsedRange.

I need to find out the last column that has data in - I want to use the "UsedRange" property of the workbook but am having problems.

This is what I'm using so far:

'Next, go through the columns in the worksheet...
    wTscWorkbook.Activate
    rngUsed = ActiveWorkbook.Sheets("Sheet1").UsedRange
    For i = 2 To rngUsed.Columns
    If Left(wTscWorkbook.Sheets("Sheet1").Cells(1, i), 1) <> "W" Then
        'The column is not a weekly figure.  Delete it.
        wTscWorkbook.Sheets("Sheet1").Columns(i).Delete
        i = i - 1
    End If
    Next i

I want to check each column in wTscWorkbook, and the column header doesn't start with "W" then I need to delete the column - as the information in all the "W" columns will eventually be pasted into another workbook.

Trouble is, the code errors on the line rngUsed = ActiveWorkbook.Sheets("Sheet1").UsedRange, error 91: Object Variable not set...

...What am I doing wrong??

Ad

[tommy bak]

Hi
I think you are missing the Set command

Set rngUsed = ActiveWorkbook.Sheets("Sheet1").UsedRange

BR
Tommy Bak

[Adaytay]

Ahhh... <d'oh> I always miss that off...

Hmmm. Ok it's assigned the used range to the required object now, but I'm having problems with the next bit - I need to check the first character in the first row (column headings), and if it's not W or S I need to delete the column.

Here's my revised code that should do that, but it's not:

'Next, go through the columns in the worksheet...
    wTscWorkbook.Activate
    Set rngUsed = ActiveWorkbook.Sheets("Sheet1").UsedRange
    For Each i In rngUsed.Columns
        If Left(wTscWorkbook.Sheets("Sheet1").Cells(1, i), 1) <> "W" And Left(wTscWorkbook.Sheets("Sheet1").Cells(1, i), 1) <> "S" Then
            'The column is not a weekly figure.  Delete it.
            wTscWorkbook.Sheets("Sheet1").Columns(i).Delete
            i = i - 1
        End If
    Next i

I've dim'd "i" as a variant as it can't be an integer. Can't get my head around why it errors on the if statement though... any ideas?

Cheers,

Ad

[Adaytay]

Cracked it...

'Next, go through the columns in the worksheet...
    wTscWorkbook.Activate
    Set rngUsed = ActiveWorkbook.Sheets("Sheet1").UsedRange
    For i = 2 To rngUsed.End(xlToRight).Column
        If Left(wTscWorkbook.Sheets("Sheet1").Cells(1, i), 1) <> "W" Then
            'The column is not a weekly figure.  Delete it.
            wTscWorkbook.Sheets("Sheet1").Columns(i).Delete
        End If
    Next i

Thanks for the shove in the right direction Tommy!!

Ad

[Jacob Hilderbrand]

i in this case is an object. A range object to be more specific. so cells(1,i) makes no sense and will error out.

Dim i as Range

and try using this instead of cells(1,i)

cells(1,i.Column)

[tommy bak]

Ok, you were quicker than me :-)
Here is my solution anyway

'Next, go through the columns in the worksheet...
wTscWorkbook.Activate
Set rngUsed = ActiveWorkbook.Sheets("Sheet1").UsedRange
For i = rngUsed.Columns.Count To 1 Step -1
    If Left(wTscWorkbook.Sheets("Sheet1").Cells(1, i), 1) <> "W" And Left(wTscWorkbook.Sheets("Sheet1").Cells(1, i), 1) <> "S" Then
        'The column is not a weekly figure.  Delete it.
        wTscWorkbook.Sheets("Sheet1").Columns(i).Delete
        i = i - 1
    End If
Next i

BR
Tommy

[Jacob Hilderbrand]

Adaytay: Your above solution will run, but it will mess up as you delete the columns. You need to loop backwards.

For i = Range("IV1").End(xlToLeft).Column To 2 Step -1

[Adaytay]

Thanks for the input guys. DRJ, as the columns I want to delete are 4 columns apart at all times the solution I came up with will work ok (I've tested it and it works fine!). Thanks though - good advice for the future!

Now then, onto the next stage.

I need to transfer the information from the last eight columns on this sheet to another sheet in another workbook. Here's my code so far:

'The UsedRange object updates whenever columns are deleted, so we can re-use that to get the last column...
    With wTscWorkbook.Sheets("Sheet1")
    .Range(.Cells(1, (rngUsed.End(xlToRight).Column) - 8), .Cells(999, rngUsed.End(xlToRight).Column)).Select
    End With
Selection.Copy
    wMaster.Sheets("Raw Data").Activate
    Sheets("Raw Data").Range("B1").Select
    Selection.Paste

It works up to a point (having trouble getting it to paste properly!). Is there a better way to do this, maybe without needing to use the clipboard?

Thanx in advance,

Ad

[tommy bak]

I don't know if it's better but it works for me

With wTscWorkbook.Sheets("Sheet1")
        Set rng = wTscWorkbook.Sheets("Sheet1").Cells(1, .UsedRange.Columns.Count - 7)
        End With
        rng.Resize(1000, 8).Copy wmaster.Sheets("Raw Data").Range("B1")

Br
Tommy Bak

[tommy bak]

If you don't want to involve the clipboard, you could use this

With wTscWorkbook.Sheets("Sheet1")
        Set rng = wTscWorkbook.Sheets("Sheet1").Cells(1, .UsedRange.Columns.Count - 7)
        End With
        wmaster.Sheets("Raw Data").Range("B1").Resize(1000, 8).Value = rng.Resize(1000, 8).Value

br
Tommy Bak

[Adaytay]

Cracked it - thanks for your help guys... sometimes a gentle shove in the right direction is all I need - I've just finished the system off and sent it for approval!

Cheers again,

Ad