Excel VBA looping through records

mcharles

New Member
Joined
Aug 9, 2004
Messages
8
Good morning to all,

I'm looking to remove unwanted records from my excel file. I know that the first cell I want to save contains the word "From". All other records above that cell I want to delete. For example:

A1 cell contains "Today is Friday"
A2 cell contains "123456"
A3 cell contains "From"
A4....A20 cells contain "Good Data"

I would like to remove all records until I reach a cell that contains "From" in column A. So, I would like to loop through all the records until I find "From". In the example above, I want to remove the first two records, and save all the records starting with the "From" record. So after removing the unwanted records, I would be left with:

A3 cell contains "From"
A4....A20 cells contain "Good Data"


Hope this makes sense. Thanks in advance for any assistance. I've used this board in the past, and you guys ROCK!

Mike C.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Code:
Sub DeleteHeaderData()
   Dim R as range

For each R in range("A1",range("A65535").end(xlup))
       If r.value = "From then 
            r.rows.entirerow.delete
            exit sub
       End if
R.rows.entirerow.delete
next

End sub

HTH
Cal
 
Upvote 0
Thanks for the example!!

Unfortunately, I'm getting an "object required" error at line:

If R.Value = "From" Then
 
Upvote 0
Noticed a typo in mine, did you add the missing qoute?

If r.value = "From then

Cal
 
Upvote 0
I didn't encounter your error, but did find a problem with the code. I tested this and it works.

Code:
Private Sub CommandButton1_Click()

Dim R As Range

For Each R In Range("A1", Range("A65535").End(xlUp))
       If R.Value = "From" Then
            Rows("1:" & R.Row).EntireRow.Delete
            Exit Sub
       End If
Next


End Sub
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top