Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Last Cell In Row
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 -- ajociu ----------------------------------------------------------------------- ajocius's Profile: http://www.excelforum.com/member.php...fo&userid=1769 View this thread: http://www.excelforum.com/showthread.php?threadid=39166 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Last Cell In Row
Tony
one way, for the activecell: Dim LastColumn As Integer LastColumn = Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Column MsgBox LastColumn Regards Trevor "ajocius" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Last Cell In Row
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing Options | Excel Discussion (Misc queries) | |||
Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing | Excel Discussion (Misc queries) | |||
Populate a cell if values in cell 1 and cell 2 match cell 3 and 4 | Excel Worksheet Functions | |||
How to create/run "cell A equals Cell B put Cell C info in Cell D | Excel Discussion (Misc queries) | |||
Question: Cell formula or macro to write result of one cell to another cell | Excel Programming |