Some basic VBA questions

[curiousgeorge408]

I have some basic VBA questions.

I am traversing a worksheet with the following pseudocode. Note: For
my purposes, it is important that I traverse row-by-row.


with worksheets("Sheet1")
for r = 1 to 1092 ' for each row
...determine n... ' last used column in row
for c = 1 to n ' for each column
...some work...
next c
if ...some condition... then
...some work...
if ...some other condition... then goto nextrow
...some more work...
end if
nextrow:
next r
end with


Questions ....

1. Can I replace 1092 (last used row number) with some non-constant
reference, ideally a property?

Something like .Rows.Count; but that always yields 65536 (I'm
using Excel 2003). If I selected the rows first in Excel, I could use
Selection.Rows.Count. But I do not want to select the rows first.


2. What's the "best" way to determine n, the index of the last used
column in a row -- ideally using a property?

Since I know that the longest row extends to column O, I wrote:

n = 15
do while isempty(.cells(r,n)): n = n - 1: if n = 0 then exit do
loop

I would prefer not to know that the longest row extends to column
O. I could search from the left, if I know there are no interstitial
empty cells; namely:

n = 0: while not isempty(.cells(r,n+1)): n = n + 1: wend


3. Can I replace "goto nextrow" with something else?

In C, I would write:

if (...some condition...) continue;

 

[Don Guillett]

lastrow=cells(rows.count,"a").END(XLUP).ROW
lastcol=cells(activecell.row,columns.count).END(XLTOLEFT).COLUMN

 

[Per Jessen]

Hi

Look at this example:

With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
For r = 1 To LastRow ' for each row
LastCol = .Cells(r, Columns.Count).End(xlToLeft).Column
For c = 1 To LastCol ' for each column
'...some work...
Next c

'if ...some condition... then
'...some work...
'if ...some other condition... then Next
' ...some more work...
End If
Next r
End With

Regards,
Per

 

[Rick Rothstein]

Questions 1 & 2: If you know that a certain column will always contain data
down to the end of your set of data (like an index number for example), then
you can find the last row using this (where my code assumes Column "A" is
the column always containing data to the end of the data set)...

With Worksheets("SheetX") ' use your actual sheet name
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Similarly, if you know a row that will always contain data out to the end of
your set of data, then you can use this to find the last column (where my
code assumes Row 1 is the row always containing data to the end of the data
set)...

Dim LastColumn As Long
With Worksheets("Sheet2") ' use your actual sheet name
LastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With

If there are no rows or columns that are guaranteed to have data to the end
of your data set, then you can use these functions to find the longest row
and column...

Function MaxRowInUse(Optional WS As Worksheet, Optional _
FactorInHiddenRows As Boolean = False) As Long
Dim X As Long
Dim LastRow As Long
If WS Is Nothing Then Set WS = ActiveSheet
With WS
For X = 1 To .UsedRange.Columns.Count
If Not (Not FactorInHiddenRows And Columns(X).Hidden) Then
LastRow = .Cells(.Rows.Count, X).End(xlUp).Row
If LastRow > MaxRowInUse Then MaxRowInUse = LastRow
End If
Next
End With
End Function

Function MaxColumnInUse(Optional WS As Worksheet, Optional _
FactorInHiddenColumns As Boolean = False) As Long
Dim X As Long
Dim LastColumn As Long
If WS Is Nothing Then Set WS = ActiveSheet
With WS
For X = 1 To .UsedRange.Rows.Count
If Not (Not FactorInHiddenColumns And Rows(X).Hidden) Then
LastColumn = .Cells(X, .Columns.Count).End(xlToLeft).Column
If LastColumn > MaxColumnInUse Then MaxColumnInUse = LastColumn
End If
Next
End With
End Function

Note: If you don't specify a worksheet in the first (optional) argument,
then the active sheet is used. The second optional argument is the
interesting one... it lets you determine whether to include hidden rows when
determining the maximum row that is in use; that is, if a hidden row
contains the maximum row, it will be ignored unless the second argument is
set to True. This allows you to get the maximum row for what you see on the
worksheet rather than for what any hidden data would return. I wasn't sure
which would be the most logical default for this second argument, so I chose
not factor in hidden rows (that is, the functions return the maximum row for
only the visible data); if desired, this can be easily changed in the
declaration headers for the function (change the False to True).

 

[Rick Rothstein]

Question 3: I usually try and structure my If..Then blocking so that the
"fall through" is to the line you designate as the "goto" line. Of course,
how to do this differs for different code structures; but, for your posted
code, instead of this...

if ...some condition... then
...some work...
if ...some other condition... then goto nextrow
...some more work...
end if
nextrow:
next r

I would do this...

if ...some condition... then
...some work...
if Not (...some other condition...) then
...some more work...
end if
end if
next r

 

[curiousgeorge408]

Look at this example:
[....]
    LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
[....]
        LastCol = .Cells(r, Columns.Count).End(xlToLeft).Column

Thanks. Exactly what I needed.

 

[curiousgeorge408]

instead of this...
[....]
            if ...some other condition... then goto nextrow
            ...some more work...
[....]
I would do this...
[....]
        if Not (...some other condition...) then
           ...some more work...
        end if

One of the dangerous of providing examples in their simplest, often
inanely degenerate form is that some people address the example
instead of the more general question.

My question was: is there a way to "continue" a for-loop from the
middle, as there is in C, for example?

I assume that the answer is "no", since several experienced
respondents, including you, did not mention it.

As to your comment, well, even Dijkstra eventually recanted his
categorical condemnation of goto's and realized that there are times
when a simple "goto" (or better: a more structured branch like
"continue") is more readable than increasing nesting of structured
statements and unduly long bodies of nested structured statements.

Exactly when to use one or the other is a judgment call. And it
certainly requires more information than I provided in my simple
examples.

 

[Rick Rothstein]

As you guessed... there is no equivalent to Continue other than the GoTo
statement you are already using. As to highly nested If..Then structures...
I try to avoid them when possible, falling back on subroutine calls (with
self-defining names) to simplify my code as much as possible. Yes, there are
situations where doing this might complicate things if you need to break out
of a loop early (using a function with a Boolean return value in place of a
simple subroutine might help with those situations); but, for the most part,
my code tends not to get so deep that I can't use the structured If..Then
approach I offered in my first posting.

--
Rick (MVP - Excel)

instead of this...
[....]
if ...some other condition... then goto nextrow
...some more work...
[....]
I would do this...
[....]
if Not (...some other condition...) then
...some more work...
end if

One of the dangerous of providing examples in their simplest, often
inanely degenerate form is that some people address the example
instead of the more general question.

My question was: is there a way to "continue" a for-loop from the
middle, as there is in C, for example?

I assume that the answer is "no", since several experienced
respondents, including you, did not mention it.

As to your comment, well, even Dijkstra eventually recanted his
categorical condemnation of goto's and realized that there are times
when a simple "goto" (or better: a more structured branch like
"continue") is more readable than increasing nesting of structured
statements and unduly long bodies of nested structured statements.

Exactly when to use one or the other is a judgment call. And it
certainly requires more information than I provided in my simple
examples.

 

[curiousgeorge408]

As to highly nested If..Then structures...
[....]
for the most part, my code tends not to get so deep
that I can't use the structured If..Then
approach I offered in my first posting.

To each his own. I was not advocating or arguing against one way or
another; I am not dogmatic about any particular programming style. I
have more than 40 years of experience as a computer programmer and
computer system architect, including some time teaching structured
design and structured programming techniques. I could wax
philosophical; in fact, I did until I prudently hit the delete
key . All I will say is: "all things in moderation".