Donaghy


Hi all,

I have to loop through a range of rows with an excel sheet and delete unwanted data. To identify the rows to be deleted I have to do the following:

Firstly I must start at A1 and check if it contains the value "Product", if not i go to the next row but if it does i must then delete this row and go to the next.

I must then check that the next cell contains the value "Total", if not i go to the next row but if it does i must then delete this row and go to the next.

I then need to delete each of the next rows until i find the next cell that contains the value "Product", delete this cell and then go through each of the next rows until i find the next cell that contains the value "Total".

This process is repeated 3 times.

so it sort of looks like:
-delete rows
-find product value
-don't delete rows
-find total value
-repeat from step 1 again


its a difficult task but help greatly appreciated




Re: Delete Unwanted rows based on value in cell

magicalclick


First you need to know your where last row is. And make simple loops for it. Anyway, I recommand you to write your own. To find the last row is tricky, but everything else is easy.

Sub LastRowCell()
On Error GoTo Not_Found:
Cells.Find(What:="*", After:=Cells(Rows.Count, Columns.Count), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Activate
' , SearchFormat:=False).Activate
Exit Sub
Not_Found:
Cells(1, 1).Select
End Sub

Sub MAIN()

Dim last as long, i as long

LastRowCell

last = Activecell.row

i = 1

do until i > last

do until Cells(i, 1).formularr1c1 = "Product" or i > last

i = i+1

loop

Rows(i).delete

last = last -1

do until Cells(i, 1).formularr1c1 = "Total" or i > last

i = i+1

loop

Rows(i).delete

last = last -1

do until Cells(i, 1).formularr1c1 = "Product" or i > last

Rows(i).delete

last = last -1

loop

loop

End Sub






Re: Delete Unwanted rows based on value in cell

Donaghy

Thats great thanks ...

though one more question. How do you check if a cell equals the exact value of "Product", I have cells which have "Production" and its picking this value up when checking the cell values which i don't want it to do.






Re: Delete Unwanted rows based on value in cell

magicalclick

My code is checking exactly "Product" and case sensitive, the very premitive kind.

You must be using the Excel search and replace functions, which is much more powerful. You can search it by part or by whole (match entire cell .. checkbox) in the detail portion of GUI.





Re: Delete Unwanted rows based on value in cell

Joe Dawson

I use this snippet of code to find the last used cell on a sheet:

LastRow = Sheets("Sheet1").Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row




Re: Delete Unwanted rows based on value in cell

Donaghy

thanks for help guys .. its working great now



Re: Delete Unwanted rows based on value in cell

konalion

I was trying to reuse your code (I know virtually nothing about VB) to look at a row in a spreadsheet, determine is the value in column 8 was < 100000 and if it was delete it, move on to the next row, so on and so forth till the end. Basically to end up with a spreadsheet that only contains rows of data with the value in column 8 100000 or greater.

I end up with an Run Time Error '438' - Object doesn't support this property or method at the highlighted line of code.

Sub MAIN()
Dim last As Long, i As Long
last = ActiveCell.Row
i = 1
Do Until i > last
Do Until Cells(i, 1).formularr1c1 = "Product" Or i > last
i = i + 1
Loop
Rows(i).Delete
last = last - 1
Do Until Cells(i, 1).formularr1c1 = "Total" Or i > last
i = i + 1
Loop
Rows(i).Delete
last = last - 1
Do Until Cells(i, 1).formularr1c1 = "Product" Or i > last
Rows(i).Delete
last = last - 1
Loop
Loop
End Sub

Any suggestions or replacement code to accomplish what I'm trying to do The last row check code seems to run fine without errors.

Thanks.





Re: Delete Unwanted rows based on value in cell

magicalclick

Haha, it is just a typo.

It is FormulaR1C1, not FormulaRR1C1. I wrote this code directly in here, so I didn't test the code at all. For you I think the following code should work, not tested, hehe.

Anyway, if you don't know VBA, try user friendly approach. You don't need VBA to do everything. For your case, Choose Data -> Auto Filter -> Enable. At column 8, choose custom filter. Simply say Greater or Equal to 100000. It will gives you the rows you want. And select them and delete them. That's it. The power of Excel is that you don't need VBA all the time, you can do it by hand. And you can record it if you want to playback in the future. Auto Filter is one of the best feature I have learn so far. Also try Select Region. It is going to blow your mind.

Sub MAIN()
Dim last As Long, i As Long
LastRowCell

last = ActiveCell.Row
i = 1
Do Until i > last

if CDbl(Cells(i, 8).formularr1c1) >= 10000 then

Rows(i).Delete

else
i = i + 1
loop

End Sub