Delete a row with the value is a cell

Microsoft Microsoft office excel 2007 -...
April 2, 2010 at 13:11:48
Specs: Windows XP
I have a spread sheet with tabs "Data" and "Change". The data is stored on the data tab and I can grab the data off of it. I have a unique identifier in column "A" of the "data" tab which I can locate and delete when I hard code the macro below "b". I want to reference a concatenate formula on tab "Changes" cell "B5". In the macro below, how would I reference sheet "Changes", the product of "B5" where the "b" is?

Sub Delete()

Dim startrow As Long
'starting row number here
startrow = 1
' Assuming data to check is in A Column
Do Until startrow > Cells(Cells.Rows.Count, "A").End(xlUp).Row


If Cells(startrow, 1).Value = "b" Then
Rows(startrow).Delete
Else
startrow = startrow + 1
End If
Loop

End Sub


See More: Delete a row with the value is a cell

Report •

#1
April 2, 2010 at 14:22:04
re: In the macro below, how would I reference sheet "Changes", the product of "B5" where the "b" is?

I'm confused by this question.

You can reference a cell (or range) on a specific sheet by it's Name as follows:

Sheets("Changes").Cells(RowIndex,ColumnIndex)

However, I don't know what you mean by "the product of "B5" where the "b" is.


Report •

#2
April 3, 2010 at 04:55:24
Hi,

Is this what you wanted:

Sub RowsDelete()
Dim lngRow As Long

For lngRow = Range("A" & CStr(Application.Rows.Count)).End(xlUp).Row To 1 Step -1
    If Range("A" & lngRow).Text = Worksheets("Changes").Range("B5").Text Then
        Range("A" & lngRow).EntireRow.Delete
    End If
Next lngRow
End Sub

It deletes rows in the active worksheet when a value in column A matches the contents of cell B5 on the Changes worksheet.
Regards


Report •

#3
April 4, 2010 at 05:59:49
The second response work perfectly. Thank you.

To clarify, my usage of "Product", I meant the resulting text of the concatenate that was in cell "B5"


Report •
Related Solutions


Ask Question