# Deleting Rows (more complicated)

#### bigmacneb

##### Board Regular
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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### Von Pookie

##### MrExcel MVP
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.

#### sweater_vests_rock

##### Well-known Member
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.

#### bigmacneb

##### Board Regular
I suppose I could determine x by typing in STOP or something like that. does that help?

#### sweater_vests_rock

##### Well-known Member
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?

#### bigmacneb

##### Board Regular
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.

#### sweater_vests_rock

##### Well-known Member
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.

#### bigmacneb

##### Board Regular
Sweater, thanks its almost there, however it deletes one row in addition to the last cell. how to I change that?

#### sweater_vests_rock

##### Well-known Member
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.

#### bigmacneb

##### Board Regular
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.

Replies
9
Views
204
Replies
6
Views
153
Replies
4
Views
340
Replies
6
Views
258
Replies
1
Views
160

1,195,632
Messages
6,010,804
Members
441,569
Latest member
PeggyLee

### 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.

### Which adblocker are you using?

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

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