Delete / Clear all Rows begining at Cell "X"

[somail]

Hi everyone, I am back for more help.

Here is what I am trying to do:

1. Search through a range starting from (B3: User_defined) and look for a blank cell. The range will not be any more than 400 rows by 10 columns. (The Loop for this works already, but if you know something more efficient feel free to suggest)

2. When a blank cell is found I want that row and all rows below it to be deleted. (This includes all cells in and out of the ealier defined range). The variable "CellCount" already tells me which cell is empty, I just need to translate that into a row.

3. I then want the row number to be saved into another variable "RowNumber" so I can keep track of everything.




Sub RowFormat()

Dim CellCount As Integer
Dim RowNumber AS Integer

Do Until Worksheets("Prices New").Range(Cells(3, 2), Cells(400, StockNum + 1)).Cells(CellCount) = ""



CellCount = CellCount + 1

Loop

End Sub



As always, Thank you for your help.

[mikerickson]

This avoids looping.
Sub Macro1()
Dim BlankCell As Range
Dim rowNumber As Long

With Worksheets("Prices New").Range(Cells(3, 2), Cells(400, StockNum + 1))
Set BlankCell = .Find(What:="", After:=.Item(.Cells.Count), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
End With
If BlankCell Is Nothing Then
MsgBox "No blank cell, nothing deleted."
Else
With BlankCell.Parent.UsedRange
Range(.Item(.Cells.Count), BlankCell.EntireRow).ClearContents
End With
rowNumber = BlankCell.Row
End If
End Sub

[somail]

That is awesome. Thank you very much. I never knew about the .find method. That is a very powerful tool.

Two follow up questions, if you do not mind:

1) When you declare a "range" object, how does excel handle that? Is memory allocated dynamically when the range is finally defined. Also, is there another type of object I can use for "single cell" variables (i.e. A1), or is range best suited for this?

2) I do not want anyone to spend a lot of time writing, but can someone explain this statement the code below? I could not find what .Parent.UsedRange is. Nor do I understand what value .Item(.Cells.Count) would be. Is that the total number of cells on the worksheet?

With BlankCell.Parent.UsedRange Range(.Item(.Cells.Count), BlankCell.EntireRow).ClearContents End With

Thank you very much for your help.

[mikerickson]

1) When one declares a Range Object Excel allocates 4 bytes for that variable's use. Single Cell variables are Ranges.

2)BlankCell.Parent is the parent of BlankCell (i.e. a worksheet)
--
BlankCell.Parent.UsedRange is the used range of that worksheet
--
With BlankCell.Parent.UsedRange.
.Item(.Cells.Count)
End Withis the last cell of the used range.
--
Range(.Item(.Cells.Count), BlankCell.EntireRow) is everything in UsedRange that is on the same row as BlankCell or below.