Search excel sheet for the word total and delete enitre row if exist

[daillest319]

Im trying to write a vbs script to Search for the word TOTAL and delete the enitre row if the word exist. also delete coumn S and remove filters

 

[SkipVought]

hi,

Where's your code?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[daillest319]

sorry forgot to add it. at the moment all im able to do is format it. code is below.

Dim objApp, xlApp, filesys, path, OldName, NewName


Set xlApp = Createobject("Excel.Application")
xlApp.Visible = False
xlApp.Screenupdating = False

xlApp.Workbooks.Open "C:\Example\test.xls"
WScript.Sleep 1000
With xlApp.Workbooks("Test.xls").ActiveSheet

.Rows("1:1").Delete
.Range("A:G").Font.Name = "Arial"
.Range("A:G").Font.Size = "8"
.Columns.AutoFit
.Range("A:Z").HorizontalAlignment = -4131
.Range("A:A").ColumnWidth = "9.29"
.Range("B:B").ColumnWidth = "3"
.Range("C:C").ColumnWidth = "1.29"
.Range("D:D").ColumnWidth = "3"
.Range("E:E").ColumnWidth = "3"
.Range("F:F").ColumnWidth = "3"
.Range("G:G").ColumnWidth = "3"
.Range("H:H").ColumnWidth = "3"
.Range("I:I").ColumnWidth = "3"
.Range("J:J").ColumnWidth = "3"
.Range("K:K").ColumnWidth = "8"
.Range("L:L").ColumnWidth = "3"
.Range("M:M").ColumnWidth = "5"
.Range("N:N").ColumnWidth = "3"
.Range("O:O").ColumnWidth = "10"
.Range("O:O").EntireColumn.NumberFormat = "0.00"

End With


xlApp.ActiveWorkbook.Close True
WScript.Sleep 1000
xlApp.Quit
Set xlApp = Nothing

 

[SkipVought]

I strongly recommend specifying a specific sheet name, unless it is unknown and the only sheets in the workbook.

dim rFound as object

With xlApp.Workbooks("Test.xls").Sheets("[highlight]Some Specific Sheet Name[/highlight]")
   do
     set rFound = .cells.find("TOTAL")

     if not rFound is nothing then
       rfound.entirerow.delete
     else
       exit do
     end if
   loop

End With

set rFound = nothing

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[daillest319]

Thank you. worked great i just need to remove filters...i took care of column s by using the following


.Columns("S").Delete

 

[SkipVought]

    .usedrange.autofilter

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]