Determining last row in a range. 4

[HairyHippy]

Apologies but this is a repost of a thread I started in the wrong forum but I thought I might find the answer quicker in here by reposting the question:

I have named a group of cells "Travel". They are currently empty and will be filled in by the users. I want the users to be able to add rows but the sheet is protected. I have written the code, so that it unprotects.

Sub InsertTravel()
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect "****"
    Rows("21:21").Select
    Selection.Copy
    Rows("22:22").Select
    Selection.Insert Shift:=xlDown
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Range("B22").Select
    ActiveSheet.Protect "****"
    Application.ScreenUpdating = True
End Sub

The problem is that Row 21 is currently the bottom row of the "Travel" range but it won't always be. This wouldn't be a problem but on the same sheet (and must remain there) there is a section called "Entertainment" that I need to do the same thing with. So what I need is a piece of code that looks at the range "Travel" and automatically selects the last row in that range.

 

[xlbo]

same as before

TheLastRow = cells(65536,1).end(xlup).row

as long as you have data in column A

You theen use TheLastRow instead of your row numbers

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[HairyHippy]

Both of the FAQs assume that there is data in the range named "Travel". That is not the case, they are empty.

How does TheLastRow = cells(65536,1).end(xlup).row refer to the last row in the named range "Travel"?

 

[xlbo]

apologies - misread the question

In which case

TheLastRow = Range("Travel").rows.count

as long as "Travel" starts in row 1 you should be fine - if not, just use an offset which is the same number as the start row of "Travel"

e.g. if "Travel" starts in row 10 then you should use

TheLastRow = Range("Travel").rows.count + 10

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

More generally

With [Travel]
 lFirstRow = .Row
 lLastRow = lFirstRow + .Rows.Count - 1 
End With

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[xlbo]

nice Skip - necer thought of that - that's a relly good way to deal with ranges that don't start in row 1

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[HairyHippy]

Sorry I am now confused, what am I substituting Rows("21:21").select with?

 

[SkipVought]

You asked, "So what I need is a piece of code that looks at the range "Travel" and automatically selects the last row in that range."

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[SkipVought]

It seems that your REALY question should have been, "how can I change a range name reference as I add/Delete rows of data?"

How can I rename a table as it changes size faq68-1331

Your macro method is NOT what I would recommend!

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[HairyHippy]

Sorry, that's not what I want to do, I want the code to find the last row in the range and then copy it. This row would then be inserted above or below the row that it has just copied but it needs to find that last row first.

 

[SkipVought]

Sub InsertTravel()
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect "****"

    With cells(lLastRow, "A")
       .Entirerow.Copy
       with .Offset(1)
          with .EntireRow
             .Insert Shift:=xlDown
             .Borders(xlEdgeTop).LineStyle = xlNone
          end with
          .select
       end with
    end with

    ActiveSheet.Protect "****"
    Application.ScreenUpdating = True
End Sub

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}