PDA

View Full Version : Solved: Passing the Used Range to a range object...



Adaytay
07-21-2004, 02:39 AM
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... :dunno

...What am I doing wrong?? :help

Ad

tommy bak
07-21-2004, 02:44 AM
Hi
I think you are missing the Set command

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

BR
Tommy Bak

Adaytay
07-21-2004, 03:00 AM
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
07-21-2004, 03:10 AM
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
07-21-2004, 03:13 AM
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
07-21-2004, 03:14 AM
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
07-21-2004, 03:19 AM
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
07-21-2004, 03:57 AM
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
07-21-2004, 04:24 AM
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
07-21-2004, 04:45 AM
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
07-21-2004, 05:00 AM
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