Deleting Rows in Excel

[eargo]

I'm pretty new to VBA, but I am having problems getting a macro to delete any rows. I have a command button, with the following code:


Private Sub CommandButton4_Click()
' Delete a record

Set MySheet = Worksheets("Sheet1&quot

MySheet.Range("A2:E2&quot.Delete Shift:=xlUp

End Sub

However when I use it, I get a 'delete object failed in range class' error. Also, could somebody explain how you use to 'Selection' object to group rows. For example, I have a list of rows, and I want to select them all, before applying a delete?

Thanks for the help!

 

[xlbo]

Rows(4:8).entirerow.delete shift:=xlup

would be your syntax for multiple rows
To pass a variable, use

x = 4
y = 8
Rows(x & ":" & y).entirerow.delete

Rgds
~Geoff~

 

[eargo]

Using Rows(4:8).entirerow.delete shift:=xlup I receive a syntax error - the code won't even compile. I tried defining the sheet it was acting on first, so I had MySheet.Rows(4:8).entirerow.delete shift:=xlup - no luck tho yet

 

[andrew299]

tHIS BIT OF CODE WORKS

Rows("4:8&quot.Select
Selection.Delete Shift:=xlUp

Andrew299

 

[eargo]

Tried replacing the : with ,'s but, while that compiles it fails to execute. Get the error: 'Application Defined or Object Defined Error' Code is:

Private Sub CommandButton4_Click()
' Delete a record

Rows(1, 1).EntireRow.Delete shift:=xlUp

End Sub

I can't see why such a simple operation is making itself so difficult - oh well :\

 

[eargo]

Thanks xlbo & Andrew, That bit works Andrew.

 

[andrew299]

Changing

Rows("4:8&quot.Select

to the location of a cell eg

range("a1&quot.select

or

cells(1,1).select

or range of cells will also delete the entirerow that that cell is in

 

[xlbo]

ooops no " "

range("A5&quot.entirerow.delete will also delete row 5 Rgds
~Geoff~

 

[eargo]

One more thing, is it possible to delete a selection of cells instead of the entire row. I ask because I have embedded command buttons at the edge of the sheet, and their position is messed up when rows are purged... I want to delete Cells *current row, column 1 to *currentrow, column 13 - shifting UP the cells. If you could provide me with code examples, as before, be most helpful

Many Thanks for all the help

 

[andrew299]

xx = your current row which you will need to define a way of recognising.

Range(Cells(xx, 1), Cells(xx, 13)).Select

For example in my spreadsheets I tend to have to put totals at the bottom so I count all cells down to the point where the cell is empty. This number would then be xx

then

selection.delete shift:xlup

 

[xlbo]

Or as an alternative
Range("A" & activecell.row & ":M" & activecell.row).delete shift:= xlup Rgds
~Geoff~

 

[eargo]

Thanks guys - much appericated the help!

Peter.