Deleting Rows (more complicated)

bigmacneb

Board Regular
Joined
Jul 12, 2005
Messages
93
I have a Macro which runs and essentially pastes data into 150 rows. However this macro is universal and therefore will have varying data between one row and 150 rows. I need a macro to delete the rows from row 150 to a specific row (but this will constantly change depending on which worksheet the macro is being applied to) How can I do this? Thanks all.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
How is the "specific row" being determined?

For example, if column A contains numbers 1-150, you could do this with simple math.

So if there were only 125 rows copied on this occasion, the last row found would be labeled 125. 125-1=124, which is the number of rows up you would need to offset to reach the cell that contains the "1" label.
 
Upvote 0
bigmac.

just so i am understanding you, you want a macro which will delete rows 150 -> row x, where x is determined by the specific worksheet upon which you are operating the macro. is this correct? if so, how would you determine x?

ben.
 
Upvote 0
bigmac.

what we mean is, how would/do you, as the user, determine the number of rows you wish to delete? that is, how would you determine where to put STOP? is it the first blank row? a change in a value? etc.

ben.
I suppose I could determine x by typing in STOP or something like that. does that help?
 
Upvote 0
I would determine to put the word STOP in when it is the cell after the last blank cell in column A. Columns B through S will have data all the way through row 150. The only way to determine which rows to delete would be in column A, two blank cells after the last entry. Hopefully that explains it better.
 
Upvote 0
big.

quite helpful. i think this should do what you want.
Code:
Sub DeleteRows()

Dim MyDeleteRange As Range
Dim StartRow As Long

StartRow = 150 '<--Delete range begins at row 150
StartCol = 1 '<--Delete range determined by column A
LastRow = ActiveSheet.Rows.Count '<--Number of rows on the worksheet

Set MyDeleteRange = Range(Cells(StartRow, StartCol), Cells(LastRow, StartCol).End(xlUp))
Set MyDeleteRange = MyDeleteRange.Resize(MyDeleteRange.Rows.Count + 1, StartCol).EntireRow

MyDeleteRange.Delete

End Sub
as always, please test this on a copy of your worksheet, as macros CANNOT be undone.

hope this helps :)
ben.
 
Upvote 0
Sweater, thanks its almost there, however it deletes one row in addition to the last cell. how to I change that?
 
Upvote 0
big.

glad we're close :) i think the following will take care of your last remaining issue.

if the extra row (being deleted) is at the beginning, change

Code:
StartRow = 150
to
Code:
StartRow = 151
if the extra row (being deleted) is at the end, change
Code:
Set MyDeleteRange = MyDeleteRange.Resize(MyDeleteRange.Rows.Count + 1, StartCol).EntireRow
to
Code:
Set MyDeleteRange = MyDeleteRange.Resize(MyDeleteRange.Rows.Count, StartCol).EntireRow
hope this gets it. ben.
 
Upvote 0
ben, neither of those worked. its still deleting the last entry in column A.
Try this-Info in column A is input every other row for say 30 rows. Info in columns B, C, and D is input for 150 rows. So Column A's last entry is on row 30, I need row 32 through 150 deleted. As it is written it deletes rows 30 through 150. Thanks again.
 
Upvote 0

Forum statistics

Threads
1,224,271
Messages
6,177,594
Members
452,784
Latest member
talippo

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
Back
Top