VBA - Last Cell In Row

[ajocius]

Group,
I'll ask this a different way with more detail. I have a For.....Next loop that goes from row 46 to some unknown number less than 2000. I increment a counter that moves me down the first column of the spreadsheet. When a logical condition is met, I compare the cells (one for one) in that row with the cells on another sheet (one for one) in the same row. Everything here works fine. The problem I'm having is one of efficiency. The row I'm on can have a variable number of cells with data. I want to find the last cell in the row that has data. I want to save this number to a variable. Presently when I'm comparing cells within the row, once I get to the end of the data in the row, the comparing continues until column 52, at which the process begins again. Note that a row can have cells with no data in them before the last cell with data. Some rows can have 20 plus cells with no data in either worksheets cells for the row I'm working in. So there is 20+ rows of needless comparing. Its probably still clear as mud, but hopefully I can get something. Oh, BTW, everyone responding in this group have been professional and educating. Thank you for all your assistance.

A budding VBA programmer..........

Tony

[Trevor Shuttleworth]

Tony

one way, for the activecell:

Dim LastColumn As Integer
LastColumn = Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Column
MsgBox LastColumn

Regards

Trevor


"ajocius" <[email protected]> wrote in
message news:[email protected]...
>
> Group,
> I'll ask this a different way with more detail. I have a
> For.....Next loop that goes from row 46 to some unknown number less
> than 2000. I increment a counter that moves me down the first column
> of the spreadsheet. When a logical condition is met, I compare the
> cells (one for one) in that row with the cells on another sheet (one
> for one) in the same row. Everything here works fine. The problem I'm
> having is one of efficiency. The row I'm on can have a variable number
> of cells with data. I want to find the last cell in the row that has
> data. I want to save this number to a variable. Presently when I'm
> comparing cells within the row, once I get to the end of the data in
> the row, the comparing continues until column 52, at which the process
> begins again. Note that a row can have cells with no data in them
> before the last cell with data. Some rows can have 20 plus cells with
> no data in either worksheets cells for the row I'm working in. So
> there is 20+ rows of needless comparing. Its probably still clear as
> mud, but hopefully I can get something. Oh, BTW, everyone responding
> in this group have been professional and educating. Thank you for all
> your assistance.
>
> A budding VBA programmer..........
>
> Tony
>
>
> --
> ajocius
> ------------------------------------------------------------------------
> ajocius's Profile:
> http://www.excelforum.com/member.php...o&userid=17695
> View this thread: http://www.excelforum.com/showthread...hreadid=391667
>

[Greg Wilson]

My take on your post is that there are often gaps in the data in the rows
that meet the specified condition. Also, the the last cell with data in each
of the rows is also not necessarily in column 52 (i.e. AZ). Therefore, there
is a lot of unnecessary iteration through cells.

The appended code loops down column A starting in the 46th cell (A46) of
Sheet1 to the last cell with data in column A. Each time a cell is found that
meets a specified condition, it sets the rng2 variable only to the cells
within that row up to the 52nd column that contain constants (i.e. blanks are
excluded and can be noncontiguous). It then iterates only through these
nonblank cells in column 1 to 52, comparing their values to those in Sheet2
with the same address. For demo purposes, the comparison is done via MsgBox.
The arbitrary specified condition is that the cell values in the first column
must equal "Test 1234".

Sub test()
Dim rng1 As Range, rng2 As Range
Dim c As Range, cc As Range
Dim rw As Long
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
rw = ws1.Cells(Rows.Count, 1).End(xlUp).Row
Set rng1 = ws1.Range(ws1.Cells(46, 1), ws1.Cells(rw, 1))
For Each c In rng1.Cells
If c.Value = "Test 1234" Then
Set rng2 = c.Resize(1, 52)
Set rng2 = Intersect(rng2, _
rng2.SpecialCells(xlCellTypeConstants))
For Each cc In rng2.Cells
MsgBox cc.Value & vbCr & _
ws2.Range(cc.Address).Value
Next
End If
Next
End Sub
"ajocius" wrote:

>
> Group,
> I'll ask this a different way with more detail. I have a
> For.....Next loop that goes from row 46 to some unknown number less
> than 2000. I increment a counter that moves me down the first column
> of the spreadsheet. When a logical condition is met, I compare the
> cells (one for one) in that row with the cells on another sheet (one
> for one) in the same row. Everything here works fine. The problem I'm
> having is one of efficiency. The row I'm on can have a variable number
> of cells with data. I want to find the last cell in the row that has
> data. I want to save this number to a variable. Presently when I'm
> comparing cells within the row, once I get to the end of the data in
> the row, the comparing continues until column 52, at which the process
> begins again. Note that a row can have cells with no data in them
> before the last cell with data. Some rows can have 20 plus cells with
> no data in either worksheets cells for the row I'm working in. So
> there is 20+ rows of needless comparing. Its probably still clear as
> mud, but hopefully I can get something. Oh, BTW, everyone responding
> in this group have been professional and educating. Thank you for all
> your assistance.
>
> A budding VBA programmer..........
>
> Tony
>
>
> --
> ajocius
> ------------------------------------------------------------------------
> ajocius's Profile: http://www.excelforum.com/member.php...o&userid=17695
> View this thread: http://www.excelforum.com/showthread...hreadid=391667
>
>