Deleting Rows

[Randal]

I have two cell names: TopOfRange and BottomOfRange. Both are empty cells. In between these cells are some rows that I want to delete, but I need to keep the named cell there and available.

I am not good with offset command, but I can write this in English.

What I want to do is in sheet1 select TopOfRange, offset down one row, if that cell is BottomOfRange? Stop (there are no rows to delete). If that offset one row is not BottomOfRange, select those rows until BottomOfRange is found, then offset one row up, copy the selected rows in that range to another sheet (Sheet2). Go back to Sheet1 and goto that selected range that was copied and delete the rows.

-R-

 

[PCL]

Where do you want to copy data in sheet2: Last row??

 

[PCL]

Assuming you want to copy data after the last row in sheet2 try next code.

Sub Copy_Delete_Rows()
Dim FIRSTROW As Long
Dim LASTROW As Long
Dim LLAST As Long
    Sheets("Sheet1").Select
    LLAST = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
    FIRSTROW = Range("TopOfRange").Row
    LASTROW = Range("BottomOfRange").Row
    Rows(FIRSTROW + 1 & ":" & LASTROW - 1).Copy Destination:=Sheets("Sheet2").Cells(LLAST, "A")
    Rows(FIRSTROW + 1 & ":" & LASTROW - 1).ClearContents
End Sub

 

[Randal]

This works great!
Might I bother w/ one more thing? When the data is added to Sheet2 I need to rename a range on that page. Before the data is added there is a range called: Name1, now that there are a few more line in that sheet I need to expand Name1 to include them.

Row1 is just column headers and columnA has all blanks.
Thanks, -R-

 

[PCL]

Let's use next code.
Name1 is used to evaluate the last row in sheet2 where to copy data.

Sub Copy_Delete_Rows()
Dim FIRSTROW As Long
Dim LASTROW As Long
Dim LLAST As Long
Dim MyADD
    LLAST = Sheets("Sheet2").Cells(Rows.Count, Range("Name1").Column).End(xlUp).Row
    Sheets("Sheet1").Select
    FIRSTROW = Range("TopOfRange").Row
    LASTROW = Range("BottomOfRange").Row
    Rows(FIRSTROW + 1 & ":" & LASTROW - 1).Copy Destination:=Sheets("Sheet2").Cells(LLAST + 1, "A")
    Rows(FIRSTROW + 1 & ":" & LASTROW - 1).ClearContents
    LLAST = Sheets("Sheet2").Cells(Rows.Count, Range("Name1").Column).End(xlUp).Row
    MyADD = Range("Name1").Address
    MyADD = "=sheet2!" & Left(MyADD, InStrRev(MyADD, "$")) & LLAST
    ActiveWorkbook.Names.Add Name:="Name1", RefersTo:=MyADD
    
End Sub

 

[Randal]

While working with this... I noticed this.

Rows(FIRSTROW + 1 & ":" & LASTROW - 1).ClearContents

This is suppose to delete the rows and shift cells up... currently just clears contents.

Also having issues redefining the range Name1

Sub ReDefineName1()
Dim MyAdd '
ActiveWorkbook.Names("TheName1").Delete 'Hates this line
Application.Goto Reference:="HCName1"
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

'Want to add an additional row to the bottom of the Name1 range

ActiveWorkbook.Names.Add Name:="Name1", RefersTo:=MyAdd
Range("A2").Select
End Sub

-R-

 

[PCL]

Rows(FIRSTROW + 1 & ":" & LASTROW - 1).ClearContents

This is suppose to delete the rows and shift cells up... currently just clears contents.
Yes, if you want delete row use:

    Rows(FIRSTROW + 1 & ":" & LASTROW - 1).Delete

'Want to add an additional row to the bottom of the Name1 range

perhaps can you use:

    LL = Range("Name1").Row + Range("Name1").Rows.Count
    Rows(LL + 1).Insert Shift:=xlDown

If you want to enlarge Name1 one row down

    MyADD = Range("Name1").Address
    LL = Right(MyADD, (Len(MyADD) - InStrRev(MyADD, "$")))
    MyADD = "=sheet2!" & Left(MyADD, InStrRev(MyADD, "$")) & LL + 1
    ActiveWorkbook.Names.Add Name:="Name1", RefersTo:=MyADD

 

[Randal]

Here is the deal thus far... the following adds a row but not to the range. It does not extend the Name1 to include that new blank row as part of the Name1 defination.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>

LL = Range("Name1").Row + Range("Name1").Rows.Count<o:p></o:p>
Rows(LL + 1).Insert Shift:=xlDown<o:p></o:p>

<o></o>
On the next one:

<o:p></o:p>
MyADD = Range("Name1").Address<o:p></o:p>
LL = Right(MyADD, (Len(MyADD) - InStrRev(MyADD, "$")))<o:p></o:p>
MyADD = "=sheet2!" & Left(MyADD, InStrRev(MyADD, "$")) & LL + 1<o:p></o:p>
ActiveWorkbook.Names.Add Name:="Name1", RefersTo:=MyADD
<o:p>

</o>
Not getting this one at all. Having all kinds of compile errors on "Right", "Left" .....<o></o>
<o></o>
What Dim's are needed to make this work?<o></o>
Dim Right as ?,
Dim Left as ?,
Dim InStrRev as ?<o></o>
by LL are you meaning LLAST or is this a new Dim too?
<o></o>
<o></o>
The reason for the blank line add at the bottom of the range is because when this range is viewed in a listbox.. the bottom record is not visible and the horizontal scrollbar covers that record.<o></o>
<o></o>
Thanks for hangin w/ me on this one. -R-<o></o>

 

[PCL]

Perhaps I've not been clear enough.
Next macro will add an extra row at the bottom of the name Name1
eg
Before Name1 = A2:A5
After Name1 = A2:A6

Sub Enlarge_Name1()
Dim MyADD
Dim LL As Long
    MyADD = Range("Name1").Address
    LL = Right(MyADD, (Len(MyADD) - InStrRev(MyADD, "$")))
    MyADD = "=sheet2!" & Left(MyADD, InStrRev(MyADD, "$")) & LL + 1
    ActiveWorkbook.Names.Add Name:="Name1", RefersTo:=MyADD
End Sub

The other is used to add a row OUTSIDE and BELOW Name1

 

[Randal]

Compile Error: Can't find project or library

Macro stops on the word "Right" in the second line of the code. -R-