Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

modify a macro to find the selected range

  Asked By: Albert    Date: Nov 03    Category: MS Office    Views: 770
  

I don't know how to find the range since the range
could vary according to the report output from Access
I want to modify this script so I don't have to select
the range before I run the script.

I tried inserting the With/end With section to
automatically select the range by counting itself but
it still doesn't work unless I select the range first.


I have a question on the original script also. Why
does it say it works backwards? I found this original
script here:
http://www.ozgrid.com/VBA/VBACode.htm

Private Sub delBlankRows1()
'Deletes the entire row within the selection if the
ENTIRE row contains no data.


Dim i As Long
Dim rngToSearch As Range
'With ActiveSheet
'Set rngToSearch = .Range(.Range("A1"),
'.Cells(Rows.Count, "A").End(xlUp))
'End With



'We work backwards because we are deleting rows.
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i))
= 0 Then
Selection.Rows(i).EntireRow.Delete
End If
Next i

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

Share: 

 

4 Answers Found

 
Answer #1    Answered By: Muntasir Bashara     Answered On: Nov 03

It works  backwards because then the rows  stay where you are until you have
processed them. Otherwise you have to move down if you don't delete a row  and
stay still if you do delete. = more complicated looping.

 
Answer #2    Answered By: Cadencia Bernard     Answered On: Nov 03

The commented-out part of your code is setting a variable with a range  of
rows that represent what you want to work  through.

The later part of your code works  with "Selection".

You have never actually selected  the area you found, so it's working with
whatever selection  you had when you entered the code.

Instead of Selection, use rngToSearch, and it'll probably work. Or do a
select of rngToSearch.

The reason it works backwards is that if you worked forwards you would have
to compensate for the situation of two consecutive blank rows. I.e. (say
they're 10 and 11)

i = 10 (set by your For statement)
row (i) is blank, delete it, row  11 moves to row 10
"Next i" - i is now 11
row(i) is not blank (remember it was row 12 originally)
etc

Working backwards eliminates this problem. I.e. delete row 11. Row 10
stays where it is. "Next i" now moves to 10.

 
Answer #3    Answered By: Patty Freeman     Answered On: Nov 03


Thank you. It seems like that backward concept is
important.

 
Answer #4    Answered By: Johnathan Nelson     Answered On: Nov 03

Yes indeed. Order of processing is important whenever items are being
inserted or deleted. It's not usually so important at other times.

For a situation like yours, it is certainly possible to do the work  in the
forward direction, but the code would be approximately twice as complex: an
inner loop would be required, and the moving last row  would need to be
accounted for.

 
Didn't find what you were looking for? Find more on modify a macro to find the selected range Or get search suggestion and latest updates.




Tagged: