Deleting Rows

Randal

Board Regular
Joined
Mar 30, 2009
Messages
142
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-
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

PCL

Well-known Member
Joined
Jul 15, 2008
Messages
1,348
Where do you want to copy data in sheet2: Last row??
 

PCL

Well-known Member
Joined
Jul 15, 2008
Messages
1,348
Assuming you want to copy data after the last row in sheet2 try next code.
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

Board Regular
Joined
Mar 30, 2009
Messages
142
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

Well-known Member
Joined
Jul 15, 2008
Messages
1,348

ADVERTISEMENT

Let's use next code.
Name1 is used to evaluate the last row in sheet2 where to copy data.
Code:
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

Board Regular
Joined
Mar 30, 2009
Messages
142
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

Well-known Member
Joined
Jul 15, 2008
Messages
1,348

ADVERTISEMENT

Code:
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:
Code:
    Rows(FIRSTROW + 1 & ":" & LASTROW - 1).Delete

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

perhaps can you use:
Code:
    LL = Range("Name1").Row + Range("Name1").Rows.Count
    Rows(LL + 1).Insert Shift:=xlDown

If you want to enlarge Name1 one row down
Code:
    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

Board Regular
Joined
Mar 30, 2009
Messages
142
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-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
Rich (BB code):
LL = Range("Name1").Row + Range("Name1").Rows.Count<o:p></o:p>
Rows(LL + 1).Insert Shift:=xlDown<o:p></o:p>
<o:p></o:p>
On the next one:
Rich (BB code):
<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:p>
Not getting this one at all. Having all kinds of compile errors on "Right", "Left" .....<o:p></o:p>
<o:p></o:p>
What Dim's are needed to make this work?<o:p></o:p>
Dim Right as ?,
Dim Left as ?,
Dim InStrRev as ?<o:p></o:p>
by LL are you meaning LLAST or is this a new Dim too?
<o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
Thanks for hangin w/ me on this one. -R-<o:p></o:p>
 

PCL

Well-known Member
Joined
Jul 15, 2008
Messages
1,348
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
Code:
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

Board Regular
Joined
Mar 30, 2009
Messages
142
Compile Error: Can't find project or library

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

Watch MrExcel Video

Forum statistics

Threads
1,122,705
Messages
5,597,669
Members
414,163
Latest member
feblop

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top