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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.