Deleting Rows (more complicated)

[bigmacneb]

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.

 

[Von Pookie]

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]

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]

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

 

[sweater_vests_rock]

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]

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]

big.

quite helpful. i think this should do what you want.

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]

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

 

[sweater_vests_rock]

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

StartRow = 150

to

StartRow = 151

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

Set MyDeleteRange = MyDeleteRange.Resize(MyDeleteRange.Rows.Count + 1, StartCol).EntireRow

to

Set MyDeleteRange = MyDeleteRange.Resize(MyDeleteRange.Rows.Count, StartCol).EntireRow

hope this gets it. ben.

 

[bigmacneb]

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.