PDA

View Full Version : Solved: Deleting rows from selected cells.



ukdane
10-21-2009, 11:49 PM
I have a worksheet with a series of rows.
I have a delete row button.
If the user selects a single cell, then the button deletes the entire row.
Job done!

BUT if the user selects more than one cell - in a different row, then I need it to delete each row that has been selected.

I assume I can use Selection.EntireRow.Delete to delete each row.

The other part of the problem, is that another part of the workbook needs to know how many cells/rows have been selected, in order to adjust the total number of rows available. I thought I could use mynrselection = Selection.Count, but if I select 3 cells, (either in 3 adjacent cells or not), when I debug, the variable returns "6".

Any ideas?

Cheers

Bob Phillips
10-22-2009, 12:46 AM
Try



Selection.Rows.Count

p45cal
10-22-2009, 01:32 AM
Selection.Count counts cells, so probably merged cells gives you 6 when you select 3 cells.
But you're after the number of rows.
I might have worked this one to death, but while exploring this I presumed the user might select non-contiguous areas, and that these might overlap (the user might have selected the same rows twice in different columns).
Selection.rows.count gives the number of rows in the first selected block of cells only.
Try this line instead of your mynrselection = Selection.Count:
mynrselection = SelectedRowsCount(Selection)supported by a function:Function SelectedRowsCount(zz As Range)
For Each a In Union(zz.EntireRow, zz.EntireRow).Areas
SelectedRowsCount = SelectedRowsCount + a.Rows.Count
Next a
End Function
All of which seems quite convoluted for something you can see clearly highlighted on the left of the spreadsheet. I can't help thinking there must be a more elegant solution.

Added later:
The calling line can be simplified a bit by using:
mynrselection = SelectedRowsCount2supported by the function:Function SelectedRowsCount2()
For Each a In Union(Selection.EntireRow, Selection.EntireRow).Areas
SelectedRowsCount2 = SelectedRowsCount2 + a.Rows.Count
Next a
End Function

ukdane
10-22-2009, 04:30 AM
Thanks as always guys.
This time I went with P45cal's function.