Results 1 to 22 of 22

Rows.Count error in dynamic range naming

Threaded View

  1. #1
    Forum Contributor
    Join Date
    05-20-2008
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    178

    Rows.Count error in dynamic range naming

    Please help.

    The attached workbook is designed to be used by my receptionists as and when required.

    My problem is getting the dynamic ranges 'RCL' & 'LCL' to be accurate.

    At the moment I have tried one method to name 'RCL' but that only gives me one line, and another method for 'LCL' which gives me all my options but then includes thousands of blank lines.

    I have included the code below to save anyone trawling through everything.
    This is my first project proper with VBA so am learning as I go along.

    Private Sub cmdSCH_Click()
        Dim ws As Worksheet, LR As Long
        Set ws = Worksheets("CL Data")
        
        Application.ScreenUpdating = False
        
        Sheets("RCL").Select
        Cells.Select
        Selection.ClearContents
        Sheets("CL Data").Select
        
        ws.Cells(2, 3).Value = Me.cbMatR.Value
        ws.Cells(2, 4).Value = Me.cbModR.Value
        ws.Cells(2, 5).Value = Me.cbFormR.Value
        ws.Cells(2, 6).Value = Me.cbDesR.Value
        
        Range("A5:K132").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
            Range("'CL Data'!Criteria"), Unique:=False
            
        Range("A6:K132").Select
        Selection.Copy
        Sheets("RCL").Select
        Range("A1").Select
        ActiveSheet.Paste
        LR = Range("A" & Rows.Count).End(xlUp).Row
        Range("A1:A" & LR).Name = "RCL"
                
        Sheets("LCL").Select
        Cells.Select
        Selection.ClearContents
        Sheets("CL Data").Select
        
        ws.Cells(2, 3).Value = Me.cbMatL.Value
        ws.Cells(2, 4).Value = Me.cbModL.Value
        ws.Cells(2, 5).Value = Me.cbFormL.Value
        ws.Cells(2, 6).Value = Me.cbDesL.Value
        
        Range("A5:K132").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
            Range("'CL Data'!Criteria"), Unique:=False
            
        Range("A6:K132").Select
        Selection.Copy
        Sheets("LCL").Select
        Range("A1").Select
        ActiveSheet.Paste
        Range("A1:A" & Rows.Count).Name = "LCL"
        
        Sheets("Search Sheet").Select
        
        Application.ScreenUpdating = True
        
    End Sub
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1