Finding lowest used range from several columns.

[Juan Sanchez]

MDW

Try:
'=========================================================
Sub FindLastUsdRow()
' This is for your range A:F, Follow the logic for A:H

Dim nLastUsdRow As Long
Dim cnt As Integer
Dim objLookIn

nLastUsdRow = 0
For cnt = 1 To 6
If Cells(2 ^ 16, cnt).End(xlUp).Row > nLastUsdRow Then
nLastUsdRow = Cells(2 ^ 16, cnt + 1).End(xlUp).Row
End If
Next cnt

Set objLookIn = objCurrentSheet.Range(Cells(4, 1), Cells
(nLastUsdRow, 6))
' objCurrentSheet is not defined in this sub, I expect you
defined it in yours.

End Sub
'=========================================================

Hope this helps...

cheers
Juan

-----Original Message-----
Say I've got a range:

Set objLookIn = objCurrentSheet.Range("A:F")

or

Set objLookIn = objCurrentSheet.Range("A:H")

I'm doing a search through those columns using a loop. As

you can imagine, looking through all those cells takes a
lot of time. I'd like to redefine my range so that I'm
searching the smallest possible area.

Is there a way I can find what the lowest used cell is

among all the columns in a range? It might be column A,
or it might be column G, etc? Here's what I want to
define:

UPPER LEFT CELL - will ALWAYS be A4
FARTHEST COLUMN RIGHT - either F or H
LOWEST ROW DOWN - the lowest utilized row from among A-F (or A-H, depending)

As an added complication, there seem to be "values" in

some of the cells in the form of 0-length strings or
possibly spaces. I'm worried that they might throw off the
calculation. For instance, say here's a range

 

[hgrove]

Juan Sanchez wrote...
...

Sub FindLastUsdRow()
' This is for your range A:F, Follow the logic for A:H

Dim nLastUsdRow As Long
Dim cnt As Integer
Dim objLookIn

nLastUsdRow = 0
For cnt = 1 To 6
If Cells(2 ^ 16, cnt).End(xlUp).Row > nLastUsdRow Then
nLastUsdRow = Cells(2 ^ 16, cnt + 1).End(xlUp).Row
End If
Next cnt

Set objLookIn = objCurrentSheet.Range(Cells(4, 1), _
Cells(nLastUsdRow, 6))
'objCurrentSheet is not defined in this sub, I expect you
'defined it in yours.

End Sub

If a procedure call is warranted, why not a parametrized function?


Function bmr(rng As Range) As Long
Dim c As Range, t As Range, otr As Long

If rng.Areas.Count > 1 Then Exit Function

otr = rng.Row
Set rng = rng.Rows(rng.Rows.Count)

For Each c In rng.Cells

If VarType(c.Value2) <> vbEmpty Then
bmr = c.Row
Exit Function

Else
Set t = c.End(xlUp)
If t.Row >= otr And VarType(t) <> vbEmpty _
And t.Row > bmr Then bmr = t.Row

End If

Next c

End Function


This function returns 0 if the range is completely blank. However, wh
not just use a worksheet formula, such as the following array formula?

=MAX((1-ISBLANK(SomeRange))*ROW(SomeRange)

 

[Juan Sanchez]

HG

Seems to me that the OP wants to shorten a range starting
from entire column selections so that then s/he can
perform other actions within the sub.

Your function works fine, he could code the function and
then call it from the procedure but I don't know how to
call a UDF from a procedure, if possible, please explain.

The simple worksheet function works if you start with a
known range smaller than entire column, I don't understand
why but if I use: =max((1-isblank(A:F))*row(A:F)) I get
65536 as a result.

At the end, the OP wants to look for something cell by
cell with in the range, s/he does not want to check all
65536 x 6 or x 8 because it takes longer so shortening the
range to the max row used within the sub seems to work for
me...

Juan

 

[Harlan Grove]

Your function works fine, he could code the function and
then call it from the procedure but I don't know how to
call a UDF from a procedure, if possible, please explain.

Maybe I don't understand what you mean, but like so?

Function foo() As String
foo = CStr(Rnd)
End Function

Sub bar()
MsgBox "Just a number: " & foo()
End Sub

Not all VBA function procedures are or are meant to be udfs.

The simple worksheet function works if you start with a
known range smaller than entire column, I don't understand
why but if I use: =max((1-isblank(A:F))*row(A:F)) I get
65536 as a result.

Fine. It gets more complicated if entire columns must be accomodated.

=IF(COUNT(A65536:F65536),65536,MAX((1-ISBLANK(A1:F65536))*ROW(A1:F65536)))

However, that's grossly inefficient. If this would be used in VBA, better to
use soemthing like my function procedure. Or a parametrized sub procedure,
e.g.,


Sub up2bmr(rng As Range)
Dim c As Range, t As Range, bmr As Long

If rng.Areas.Count > 1 Then Exit Sub

For Each c In rng.Rows(rng.Rows.Count).Cells
If VarType(c.Value2) <> vbEmpty Then Exit Sub

Set t = c.End(xlUp)
If Not Intersect(t, rng) Is Nothing And VarType(t) <> vbEmpty _
And t.Row > bmr Then bmr = t.Row
Next c

If bmr > 0 Then
Set rng = rng.Resize(bmr - rng.Row + 1, rng.Columns.Count)
Else
Err.Raise 666, "up2bmr", "Entirely blank range"
End If

End Sub

At the end, the OP wants to look for something cell by
cell with in the range, s/he does not want to check all
65536 x 6 or x 8 because it takes longer so shortening the
range to the max row used within the sub seems to work for
me... ....

Except that your sub does nothing. By making objLookIn a local variable,
it's lost as soon as the sub exits. Either you need to use a function that
returns either the lowest row containing anything or a range spanning the
used rows or one that modifies a passed range object as a side effect.

If your sub, AS WRITTEN, had done anything useful, I might not have
responded.

 

[Juan Sanchez]

HG

I agree, my Sub as writen does nothing, it is intended for
the OP to use on his original sub instead of his
selection... what my sub does is get the selection the OP
needs so he can go on with his sub... I don't know what is
it that he wants to do with that range, but I can see from
his post that he is writing a procedure and wanted a way
to reduce from A:F to An:Fn what ever n was...

Maybe I wasn't clear enough since I included Sub.... End
Sub statements,


Thanks for explaining the funciton issue, i didn't know it
was that easy...

Juan