Find cell select row and delete

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

Can somebody please help

I have a worksheet with around 700 rows but always only 7 columns, once I apply a sort I have one blank cell in Column A. I have a macro that sorts and adds functionality to the sheet but I need to be able to find this empty cell in column A, select this row, select all rows underneath that contain numeric data and delete them. I am new to this so your patience is appreciated if I ask dumb questions
Many thanks in advance for your help

Rich
 
If I'm understanding you correctly you could tag this code onto you
macro:

Range("A2:G65536").Clearcontents

If this isn't what you're looking for let me know.

Thank
 
Rich
The following macro will do what you want. You'll have to figure out
how to take this code and put it in your code. Note that this macro, as
written, assumes your data starts in A1. Also note that this macro will
find the first blank cell in Column A and delete that row as well as all
other rows below it. Watch out for line wrap in this message. Expand this
message to full screen to see the code properly. I put an error trap into
the code in case there is no blank cell in Column A between the first and
last entry in the column. In that event the macro does nothing. HTH
Otto
Sub DeleteBlankNBelow()
Dim RngColA As Range
Set RngColA = Range("A1", Range("A" & Rows.Count).End(xlUp))
On Error Resume Next
Range(RngColA.Find(What:="", LookAt:=xlWhole), Range("A" & Rows.Count)).
_
EntireRow.Delete
On Error GoTo 0
End Sub
Rich said:
Hi,

Can somebody please help?

I have a worksheet with around 700 rows but always only 7 columns, once I
Click to expand...
apply a sort I have one blank cell in Column A. I have a macro that sorts
and adds functionality to the sheet but I need to be able to find this empty
cell in column A, select this row, select all rows underneath that contain
numeric data and delete them. I am new to this so your patience is
appreciated if I ask dumb questions.
 
Dim rng as Range, rng1 as Range
if not isempty(Range("A1")) then
On Error Resume Next
set rng = Columns(1).SpecialCells(xlBlanks)(1)
On Error goto 0
if not rng is nothing then
set rng1 = Cells(rows.count,1).End(xlup)
if rng1.Row > rng.row then _
Range(rng,rng1).Entirerow.Delete
end if
Else
Rows("1:65536").Delete
End If

If you know you will always have a blank cell and it won't ever be the A1 or
A2 cell or the last cell in the column

Range(cells(1,1).End(xldown)(1), _
Cells(rows.count,1).End(xlup)).Entirerow.Delete

--
Regards,
Tom Ogilvy


Rich said:
Hi,

Can somebody please help?

I have a worksheet with around 700 rows but always only 7 columns, once I
Click to expand...
apply a sort I have one blank cell in Column A. I have a macro that sorts
and adds functionality to the sheet but I need to be able to find this empty
cell in column A, select this row, select all rows underneath that contain
numeric data and delete them. I am new to this so your patience is
appreciated if I ask dumb questions.
 
Back
Top