Loop through Excel and delete rows that don't contain text

[crystalfun]

After subtotalling a spreadsheet in Excel I want to delete all of the detail and leave the total only. What VB code can I use to loop through the rows and delete every row except the one reading "Total."

 

[1acre4me2p]

This is written in VBA in Excel 9
Shouldn't be difficult to translate to VB5 - 6
There are probably better ways of doing it.
Thats the fun of VB doing your research.

Sub KillDetail()
Dim I As Long
Dim K As Long
I = 1

Do Until Range("A" & Str(I)).Text = "Total :-"
Range("A" & Str(I) & ":" & "G" & Str(I)).Delete
I = I + 1
Loop
End Sub

Good Luck.

 

[koukouroukou]

or use this and manipulate excel like a recordset
Dim cnnDB As ADODB.Connection
Dim rst As ADODB.Recordset


Set cnnDB = New ADODB.Connection
Set rst = New ADODB.Recordset

With cnnDB
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties&quot = "Excel 8.0"
.Open "C:\xltest.xls"
End With

Set rst.ActiveConnection = cnnDB
rst.CursorLocation = adUseClient
rst.Open "DELETE from [customers$] where [name] is null and [surname] is null "

as you can see my sheet name is customers and i write it in sql statement as [customers$] ,name and surname are 2 of my excel columns.