Consulting

Results 1 to 11 of 11

Thread: Solved: Passing the Used Range to a range object...

  1. #1
    VBAX Regular
    Joined
    May 2004
    Location
    Driffield, East Yorkshire, Egnland
    Posts
    69
    Location

    Question Solved: Passing the Used Range to a range object...

    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:

    [vba]
    '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
    [/vba]

    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

  2. #2
    VBAX Regular
    Joined
    Jun 2004
    Location
    Denmark
    Posts
    58
    Location
    Hi
    I think you are missing the Set command

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

    BR
    Tommy Bak

  3. #3
    VBAX Regular
    Joined
    May 2004
    Location
    Driffield, East Yorkshire, Egnland
    Posts
    69
    Location
    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:

    [vba] '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[/vba]

    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

  4. #4
    VBAX Regular
    Joined
    May 2004
    Location
    Driffield, East Yorkshire, Egnland
    Posts
    69
    Location
    Cracked it...

    [vba]
    '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
    [/vba]

    Thanks for the shove in the right direction Tommy!!

    Ad

  5. #5
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,710
    Location
    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)

    [vba]
    cells(1,i.Column)
    [/vba]

  6. #6
    VBAX Regular
    Joined
    Jun 2004
    Location
    Denmark
    Posts
    58
    Location
    Ok, you were quicker than me :-)
    Here is my solution anyway

    [VBA]
    '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

    [/VBA]

    BR
    Tommy

  7. #7
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,710
    Location
    Adaytay: Your above solution will run, but it will mess up as you delete the columns. You need to loop backwards.


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

  8. #8
    VBAX Regular
    Joined
    May 2004
    Location
    Driffield, East Yorkshire, Egnland
    Posts
    69
    Location
    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:

    [vba]
    '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
    [/vba]

    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

  9. #9
    VBAX Regular
    Joined
    Jun 2004
    Location
    Denmark
    Posts
    58
    Location
    I don't know if it's better but it works for me
    [VBA]
    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")
    [/VBA]
    Br
    Tommy Bak

  10. #10
    VBAX Regular
    Joined
    Jun 2004
    Location
    Denmark
    Posts
    58
    Location
    If you don't want to involve the clipboard, you could use this

    [VBA]
    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
    [/VBA]

    br
    Tommy Bak

  11. #11
    VBAX Regular
    Joined
    May 2004
    Location
    Driffield, East Yorkshire, Egnland
    Posts
    69
    Location
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •