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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
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
 

mcharles

New Member
Joined
Aug 9, 2004
Messages
8
Thanks for the example!!

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

If R.Value = "From" Then
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
Noticed a typo in mine, did you add the missing qoute?

If r.value = "From then

Cal
 

mcharles

New Member
Joined
Aug 9, 2004
Messages
8

ADVERTISEMENT

I added the quote and I'm still getting the error.
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,309
Messages
5,641,448
Members
417,209
Latest member
Agbarker

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
Top