Delete All But First 10 Rows of Named Range

[angusfire]

I have a spreadsheet that adds additional rows to a named range. That part works like a charm. I need help with a macro to reset that named range to 10 rows; i.e. delete all but the first 10 rows. It would also be needed to limit the number of rows in the range not to be less than 10 by including an error message that informs the user that the table can not be less than 10 rows.

Below is the code that I have so far. The Rest_Bid_Table sub is where I need the help.

[FONT=Verdana]Sub Add_New_Bid_Item()
Dim j As Variant
' Insert new rows to bid schedule table
' Copy formula from cell above and insert user defined number of rows
    Application.CutCopyMode = False
    j = InputBox("Number of Bid Items to Add:")
    Rows(Range("Bid_Schd").Rows.Count + Range("Bid_Schd").Cells(1, 1).Row - 1).EntireRow.Select
    Rows(Selection.Row).Copy
    Rows(Selection.Row).Resize(j).EntireRow.Insert Shift:=xlDown
    Application.CutCopyMode = True
    Range("A1").Select
End Sub[/FONT]
[FONT=Verdana]
Sub Reset_Bid_Schedule()
'Reset and limit bid schedule to a minimum of 10 rows
    Range("Bid_Schd").EntireRow.Select 'Need help in selecting all but the first 10 rows
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
End Sub[/FONT]

 

[Fluff]

How about

Sub angusfire()
   With Range("Bid_Schd")
      On Error GoTo MyError
      .Offset(10).Resize(.Rows.Count - 10).EntireRow.Delete
   End With
   Exit Sub
MyError:
   MsgBox "Table must be at least 10 rows"
End Sub

 

[angusfire]

Thanks! I knew it was something simple.

That works for the most part. How though would you limit and notify the user if they manually deleted rows that made the table less than 10 rows? I figure it would be through some worksheet change.​

 

[angusfire]

I think I figured it out somewhat. But it adds 10 more rows to the table; not resetting it to 10.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub Reset_Bid_Schedule()
'Reset and limit bid schedule to a minimum of 10 rows
    With Range("Bid_Schd")
        On Error GoTo MyError
        .Offset(10).Resize(.Rows.Count - 10).EntireRow.Delete
    End With
    Exit Sub
    
MyError:
    MsgBox "Bid Schedule must be at least 10 rows."
    If vbOK Then
        Rows(Range("Bid_Schd").Rows.Count + Range("Bid_Schd").Cells(1, 1).Row - 1).EntireRow.Select
    Rows(Selection.Row).Copy
    Rows(Selection.Row).Resize(10).EntireRow.Insert Shift:=xlDown
    Application.CutCopyMode = True
    Range("A1").Select
    End If
End Sub[/FONT]

 

[Fluff]

How about

Sub angusfire()
   With Range("Bid_Schd")
      Select Case .Rows.Count
         Case 10
         Case Is > 10
            .Offset(10).Resize(.Rows.Count - 10).EntireRow.Delete
         Case Else
            MsgBox "Must be 10 rows"
            .Name.RefersTo = .Name.RefersToRange.Resize(10)
      End Select
   End With
End Sub

 

[angusfire]

This ended up working the best for me.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub Reset_Bid_Schedule()
'Reset and limit bid schedule to a minimum of 10 rows
    With Range("Bid_Schd")
        If Range("Bid_Schd").Rows.Count < 10 Then
            Call MyError
        End If
        If Range("Bid_Schd").Rows.Count > 10 Then
            .Offset(10).Resize(.Rows.Count - 10).EntireRow.Delete
        End If
    End With
    Exit Sub
End Sub

[/FONT][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub MyError()
    Rows(Range("Bid_Schd").Rows.Count + Range("Bid_Schd").Cells(1, 1).Row - 1).EntireRow.Select
    Rows(Selection.Row).Copy
    Rows(Selection.Row).Resize(10).EntireRow.Insert Shift:=xlDown
    Application.CutCopyMode = True
    Range("A1").Select
End Sub[/FONT]

 

[Fluff]

Glad you sorted it & thanks for the feedback