VBA - Last Cell In Row

[ajocius]

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

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

Ton

 

[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

 

[Guest]

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