Results 1 to 5 of 5

vba: user defined range

This is a discussion on vba: user defined range within the Excel Questions forums, part of the Question Forums category; i have a a spreadsheet that users will constantly be adding new rows and deleting rows. I need to run ...

  1. #1
    Board Regular
    Join Date
    Jul 2005
    Posts
    84

    Default vba: user defined range

    i have a a spreadsheet that users will constantly be adding new rows and deleting rows. I need to run some code that will sort the rows in descending order, then loop thru and evaluate each row.

    So i scraped some code up that allows the user to specifiy how many rows there are.

    Sub Holdings_Range() 'this allows the user to dynamically increase or decrease the range as Vouchers expand
    NumVouchers = Cells(4, 10) 'cell J4 has number 12 in it
    Set EndRange = Range("G" & 15 + NumVouchers)
    Set StartRange = Range("A15")
    Range(StartRange, EndRange).Select
    End Sub

    The above works great as the proper range is selected.

    But now I have trouble passing that same range for sorting. I currently have it hard-coded with 15 and 27.

    ActiveWorkbook.Worksheets("perf").Sort.SortFields.Clear
    '*****NEED TO MAINTAIN RANGE AS VOUCHERS EXPAND*****
    ActiveWorkbook.Worksheets("perf").Sort.SortFields.Add Key:=Range("D15:D27"), _
    SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("perf").Sort
    '*****NEED TO MAINTAIN RANGE AS VOUCHERS EXPAND*****
    .SetRange Range("A15:G27")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With

    And then I have trouble again passing the range to a loop which is also currently hard coded with 15 and 27.

    For r = 15 To 27 Step 1
    If Cells(r, 4).Value < Range("G10").Value Then
    'Rows(r).ClearContents
    Rows(r).Font.ColorIndex = 3 'red
    End If

    Next r

    The sort and loops works fine hard-coded, but I just want to be able to pass the range from Holdings_Range() for the Sort and Loop.

  2. #2
    Board Regular
    Join Date
    Nov 2009
    Posts
    4,764

    Default Re: vba: user defined range

    Straight after, or replace, the line:
    Range(StartRange, EndRange).Select
    have:
    Range(StartRange, EndRange).Name = "RangeToSort"

    Then elsewhere in the code you can refer to that range with:
    Range("RangeToSort")

    Later on, replace:
    Code:
    For r = 15 To 27 Step 1
            If Cells(r, 4).Value < Range("G10").Value Then
                'Rows(r).ClearContents
                Rows(r).Font.ColorIndex = 3 'red
            End If
    with the likes of:
    Code:
    For each cll in Range("RangeToSort").columns(4)
            If cll.Value < Range("G10").Value Then
                'cll.entirerow.ClearContents
                cll.entirerow.Font.ColorIndex = 3 'red
            End If

  3. #3
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    12,062

    Default Re: vba: user defined range

    Code:
    Sub Holdings_Range() 'this allows the user to dynamically increase or decrease the range as Vouchers expand
    
        Dim rngData As Range, rngD As Range, cell As Range
        
        Set rngData = Range("A15:G15").Resize(Range("J4").Value + 1) 'cell J4 has number 12 in it
        'rngData.Select
        Set rngD = Range("D15").Resize(Range("J4").Value + 1)
        
        With Worksheets("perf").Sort
            .SortFields.Clear
            .SortFields.Add Key:=rngD, _
                SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
            .SetRange rngData
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
        For Each cell In rngD
            If cell.Value < Range("G10").Value Then
                cell.EntireRow.ClearContents
                cell.EntireRow.ColorIndex = 3 'red
            End If
        Next cell
    
    End Sub
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Please surround your pasted VBA code with CODE tags e.g.;
    [CODE] your VBA code here [/CODE]
    The pound # icon in the forum editor will apply the CODE tags around your selected text.

  4. #4
    Board Regular
    Join Date
    Jul 2005
    Posts
    84

    Default Re: vba: user defined range

    for some reason when I use AlphaFrog's code below, I get an error from the following line

    cell.EntireRow.ColorIndex = 3 'red

  5. #5
    Board Regular
    Join Date
    Jul 2005
    Posts
    84

    Default Re: vba: user defined range

    i found it. was missing the word "font"

    cell.EntireRow.Font.ColorIndex = 3

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com