Using Array's
I'm confused at what you're doing, but if you want to delete rows, it's usually
easier to start at the bottom and work your way to the top:
Dim iRow as long
dim FirstRow as long
dim LastRow as long
with worksheets("somesheetnamehere")
firstrow = 2 'headers could be in row 1
lastrow = .cells(.rows.count,"B").end(xlup).row
for irow = lastrow to firstrow step -1
if lcase(.cells(irow,"B").value) = lcase("delete me") then
.rows(irow).delete
end if
next irow
end with
==========
A different option is to start from the top and work down, but build a range
(not an array) that can be deleted later. This method could fail if the range
has a lot of cells and there's lots of gaps in it.
dim myCell as range
dim myRng as range
dim DelRng as range
with worksheets("somesheetnamehere")
set myrng = .range("B2",.cells(.rows.count,"B").end(xlup))
end with
for each mycell in myrng.cells
if lcase(mycell.value) = lcase("delete me") then
if delrng is nothing then
'prime the pump
set delrng = mycell
else
'add more to the delrng
set delrng = union(mycell,delrng)
end if
end if
next mycell
if delrng is nothing then
'nothing found to delete
else
delrng.entirerow.delete
end if
=====
Both are uncompiled and untested--watch for typos.
Rick S. wrote:
Hence my request for help?
This code was provided by other members and I am attempting to learn from
it. It is used in another module and functions properly, in this module I am
trying to use it to delete rows after the user makes selections. I have
found that deleting rows during selection throws off the counting during
selection and will omit rows from deletion.
The originating array code is as below, creating an array of selected sheets.
'======
Sub CopySelectSheets()
Dim N As Long
Dim ShtArray() As Variant
Dim Wks As Worksheet
For Each Wks In Worksheets
If Wks.Name < "ListA" Then
If Wks.Cells(4, "D").Value = 10 Then
N = N + 1
ReDim Preserve ShtArray(1 to N)
ShtArray(N) = Wks.Name
End If
End If
Next Wks
If N 0 Then
Sheets(ShtArray()).Copy After:=Sheets(ShtArray(N))
End If
End Sub
'======
The "goto" code scrolls to the cell that has a match.
'======
Application.Goto Reference:=Worksheets("Sheet1").Range(sh1cell.Addr ess), _
Scroll:=True
'======
Above is one string.
--
Regards
VBA.Newb.Confused
XP Pro
Office 2007
--
Dave Peterson
|