CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2006
    Location
    Wantagh,NY
    Posts
    151

    Help with VBA Excel Script

    I am trying to write a VB Script to do a few things in Excel. I am trying to filter a table based on certain criteria. It is working if I directly specify the table range (where the data is). I would like to make this more dynamic so that if the size of the table grows then the script will still work. How can set the table range to go to the last cell in the table? Please see code comments.

    Code:
    If Target.Range.Address = "$B$5" Then
    With Sheets("PAR Where is it")
    MsgBox "Event from B5 Fired"
    'specific range from A2 to L110.  What if table grows?  How to find last cell in table?
    ActiveSheet.Range("A2:L110").AutoFilter Field:=12, Criteria1:="<=5"
    ActiveSheet.Range("A2:L110").AutoFilter Field:=2, Criteria1:="MTABC"
    End With
    End If

  2. #2
    Join Date
    Jun 2010
    Location
    Germany
    Posts
    2,675

    Re: Help with VBA Excel Script

    ActiveSheet.UsedRange gives you just what the name suggests. However, that may not be exactly what you want, since you seem to be wanting to exclude the first row. A range comprising the used range except for the first row can be obtained like this:

    Code:
    Dim rngYourRange As Range, rngUsed As Range
    Set rngUsed = ActiveSheet.UsedRange
    Set rngYourRange = ActiveSheet.Range("A2:" & Chr(64 + rngUsed.Columns.Count) & Mid(Str(rngUsed.Rows.Count), 2))
    BTW, the better place to post questions about VBA, where it's more likely you get an answer, probably is the VB6 section, since VBA and VB6 are very similar.
    Last edited by Eri523; October 15th, 2012 at 12:33 PM. Reason: Code formatting
    I was thrown out of college for cheating on the metaphysics exam; I looked into the soul of the boy sitting next to me.

    This is a snakeskin jacket! And for me it's a symbol of my individuality, and my belief... in personal freedom.

Posting Permissions

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





Click Here to Expand Forum to Full Width

Featured