Range Delete Not working completely

[Terry]

I am currently writing an MS Access DB program that modifies a Excel
spreadsheet. I am search a table on the sheet and where the search values
match I am deleting cells from a row. I am deleting the first 7 columns from
the row and shifting the cells up.

The code I have works to a point. Out of the several hundred rows only a
handful are being deleted. I am deleting them row by row since they will not
always appear in sequence.

thanks

**************************************

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim BegRange As Excel.Range
Dim EndRange As Excel.Range
Dim Row as Integer

Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Open("C:\temp\Video Churn
Report_v_2007_06_22", 0, False)
Set xlSheet = xlBook.Worksheets("Q1-Insvc_Disc Data")

' This code searchs the sheet for matching variables.
' If found it sets the range to delete and then deletes them shifting up
Do While xlSheet.Cells(Row, 1).Value <> ""
If xlSheet.Cells(Row, 1).Value = "terry" Then
'MsgBox Row
Set BegRange = xlSheet.Cells(Row, 1)
Set EndRange = xlSheet.Cells(Row, 7)
xlSheet.Cells.Range(BegRange, EndRange).Delete (xlUp)
End If
Row = Row + 1
Loop

 

[Jim Thomlinson]

Change your code to move from the last row up instead of from the first row
down. The issue you are having is that when you delete cells it moves you to
the next row down which is a row that you have not yet evaluated. You then
offset down a row and miss the one you were just on.

 

[Terry]

Jim

Thanks for the response. I was just thinking the same thing and I am trying
to find the best way to do that. Is there a way to quickly tell what the
last row of a table is? The sheets are populated with other table that may
go futher down then the one I am currently working on. I know the table
starts in A2 and can vary in the number of rows it has. I don't really want
to loop thru the table row by row to find the last one if Excell has
something that works faster.

Terry

 

[meh2030]

Jim

Thanks for the response. I was just thinking the same thing and I am trying
to find the best way to do that. Is there a way to quickly tell what the
last row of a table is? The sheets are populated with other table that may
go futher down then the one I am currently working on. I know the table
starts in A2 and can vary in the number of rows it has. I don't really want
to loop thru the table row by row to find the last one if Excell has
something that works faster.

Terry







- Show quoted text -

Terry,

Here are four separate properties/methods that you can search in the
VBE Help. Hopefully one of them will give you what you are looking
for.

1. UsedRange
2. CurrentRegion
3, SpecialCells
4. End

Matt

 

[Jim Thomlinson]

I would be inclined to use find as it will be a bit more efficient...
Something like this...

Sub DeleteStuff()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String
Dim rngToSearch As Range

Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Open("C:\temp\Video Churn
Report_v_2007_06_22 ", 0, False)
Set xlSheet = xlBook.Worksheets("Q1-Insvc_Disc Data")
Set rngToSearch = xlSheet.Columns("A")
Set rngFound = rngToSearch.Find(What:="terry", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=True)

If rngFound Is Nothing Then
MsgBox "nothing to delete"
Else
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
rngFoundAll.Resize(, 7).Delete
End If

End Sub

If you wnat to find the list row in a column you can use
dim lngLastRow as long 'always use longs for rows as integer is too small

lngLastRow = xlSheet.cells(rows.count, "A").end(xlUp).Row

 

[Terry]

Jim

The code for finding the last row is exactly what I needed. I am still
developing the rest of the code but this helps.

thanks

Terry