Solved: Select variable range of rows and then delete ??

[bdsii]

Hello all....I have looked but cannot find the answer anywhere for my specific question. I figure it is easy but I cannot crack it.

I want to delete the entire rows between a cell (that will change from one instance to another) and the end of the data I have in the spreadsheet.



What i have below works but deletes row by row and takes forever. I can sort and put all the rows with PN = "" together at the end of the spreadsheet so it is easy to find the first row to delete by going to the end of the data in Column A. The PNs are in Column A.

The variable totalrows used below provides the number of rows with data and is defined earlier in the code and is
[VBA]
totalrows = ActiveSheet.UsedRange.Rows.Count
[/VBA]


Existng Code that takes too long
[VBA]
' Counter to Delete Rows where PN = ""
Dim CounterDelete As Integer
Dim RefPN As String
Range("A1").Select

For CounterDelete = 1 To totalrows
ActiveCell.Offset(1, 0).Select
RefPN = ActiveCell.Value
If RefPN = "" Then
ActiveCell.EntireRow.Select
Selection.Delete
ActiveCell.Offset(-1, 0).Select

Else
'Do Nothing

End If

Next CounterDelete

[/VBA]


Can anyone help me out here ?


Thanks!

[mikerickson]

Running this macro will delete all rows from the selected cell down to the last cell in the column of the selection. (Data in a different column, below the last data in the selected column will not be deleted.)

[VBA]With Selection
.Value = "x"
Range(.Cells(1, 1), .EntireColumn.Cells(.EntireColumn.Rows.Count, 1).End(xlUp)).EntireRow.Delete
End With[/VBA]

[lucas]

maybe if you do away with the select:
[vba]If ActiveCell.Offset(1, 0).Value = "" Then[/vba]

It appears that you are looking in the row below in the same cell for a value or not. Did you try a filter solution if that's true?

[bdsii]

Running this macro will delete all rows from the selected cell down to the last cell in the column of the selection. (Data in a different column, below the last data in the selected column will not be deleted.)

[vba]With Selection
.Value = "x"
Range(.Cells(1, 1), .EntireColumn.Cells(.EntireColumn.Rows.Count, 1).End(xlUp)).EntireRow.Delete
End With[/vba]

Thanks for the help but I am not following. How do I specify the range of rows to be selected ? Sorry but I am new to this.

[bdsii]

maybe if you do away with the select:
[vba]If ActiveCell.Offset(1, 0).Value = "" Then[/vba]

It appears that you are looking in the row below in the same cell for a value or not. Did you try a filter solution if that's true?

I didn't try a filter solution just feeling my way along here

I want to delete the rows where PN is blank and I have been doing that one row at a time which took forever.

I would be happy to replace the code above with any that will make it simpler and more efficient


Thanks!

[mikerickson]

To run the macro I posted:
Select a cell in the top row that you want deleted.
Run the macro.

All rows that have data in the column below and including the selected cell will be deleted.

[bdsii]

Mikerickson - I tried the macro provided to delete the rows and could not get it to work as I had hoped. I am attaching a sample file that may help explain my intentions.

I highlighted in yellow the cell that will be selected and I want to select starting that entire row and all those rows below to the end of the data. Basically I want to delete all the rows without a value in Column A. I have sorted the data to place all those rows to be deleted at the bottom. I want to delete the entire rows from the highlighted cells to the end of data and that location and number of rows will vary.

Thoughts ?

Thanks for your help!

[mikerickson]

The code I posted looks down from the selected cell to find the last row.
Your situation looks to other columns to find the last row.

If the rows are marked by a truely blank cell (not a formula that returns "") you could use this without needing to sort.
[VBA]With ThisWorkbook.Sheets("Sheet1").Range("A:A")
On Error Resume Next
.SpecialCells(xlCellTypeBlanks).Delete
On Error GoTo 0
End With[/VBA]

[bdsii]

How would I handle it if the data that is in Cell A for some rows is "" ?

What I would like to do is to put all the rows that I will delete together (can do by sorting) and then selecting entire rows that will be deleted and then deleting them at the same time rather than one row at a time.

Any ideas ?

[bdsii]

After tinkering with it I have figured out code that will delete a range of rows that are variable. The code below works for me. I hope it works for others that may be interested as well.

For the code below, data was deleted from Column A from previous code. It was then sorted to throw the rows with no info in Column A to the bottom which is key to the code below working.

If anyone has any alternate suggestions to accomplish the same thing, please post here :-)

I am marking this as solved.

Thanks everyone for their help and suggestions.

[vba]

Dim Rownumber As String
Dim LastRow As String

LastRow = ActiveSheet.UsedRange.Rows.Count
Range("A1048576").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
Rownumber = ActiveCell.Row

Range(Rownumber & ":" & LastRow).EntireRow.Delete
[/vba]


One other thing, the row number in the Range of 1048576 is for Excel 2007, if you need to use it for Excel 2003 replace it with 65536 instead which is the max rows for Excel 2003.

[mdmackillop]

You can do all that in one row, which caters for 2003 & 2007
[VBA]
Range(Cells(Rows.Count, 1), Cells(Rows.Count, 1).End(xlUp).Offset(1)).EntireRow.Delete

[/VBA]

[Bob Phillips]

After tinkering with it I have figured out code that will delete a range of rows that are variable. The code below works for me. I hope it works for others that may be interested as well.

For the code below, data was deleted from Column A from previous code. It was then sorted to throw the rows with no info in Column A to the bottom which is key to the code below working.

If anyone has any alternate suggestions to accomplish the same thing, please post here :-)

I am marking this as solved.

Thanks everyone for their help and suggestions.

[vba]

Dim Rownumber As String
Dim LastRow As String

LastRow = ActiveSheet.UsedRange.Rows.Count
Range("A1048576").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
Rownumber = ActiveCell.Row

Range(Rownumber & ":" & LastRow).EntireRow.Delete
[/vba]


One other thing, the row number in the Range of 1048576 is for Excel 2007, if you need to use it for Excel 2003 replace it with 65536 instead which is the max rows for Excel 2003.

Row Number and LastRow should be type Long, not String.

[bdsii]

Thanks XLD, I missed that. I appreciate the correction. It worked as it was but I want it to be correct so I will change it.

mdmackillop - Now you tell me! :-) I will give that a whirl. As you can tell I am new to VBA but it looks like some of these statements are just a matter of tacking one statement or a partial onto another statement. Where can I find info on these type of statements or code? Is it trial and error ?

[mdmackillop]

The line is more complicated than it looks. There is only one action being performed; ie Delete. The rest is to define the range.
If you had a specific range, then the code would be
[VBA]
Range("A10:A20").EntireRow.Delete
[/VBA]Because you have to determine the cells, you are simply substituting a dynamic address instead of a cell address.
If the Range was to be used for other purposes, then assign a variable to it, then you can refer to it as required
[VBA]
Dim Rng as Range
Set Rng = Range(Cells(Rows.Count, 1), Cells(Rows.Count, 1).End(xlUp).Offset(1))
Rng.EntireRow.Delete

[/VBA]

[bdsii]

Thanks for the info mdmackillop :-)