Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last used cell in row with blanks inbetween
Hi, i need to start at cell A1 and find the last used cell in the same
row using code. The only problem is that there are sometimes one or two blanks in the row.I know i can use - Selection.End(xlToRight).Select But the problem is that it stops at the blanks too. Any help with some code would be appreciated. thanks in advance. Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last used cell in row with blanks inbetween
Hi Less
Try Range("IV1").End(xlToLeft).Select In Excel 12 we must use Cells(1, Columns.Count).End(xlToLeft).Select -- Regards Ron de Bruin http://www.rondebruin.nl "Les Stout" wrote in message ... Hi, i need to start at cell A1 and find the last used cell in the same row using code. The only problem is that there are sometimes one or two blanks in the row.I know i can use - Selection.End(xlToRight).Select But the problem is that it stops at the blanks too. Any help with some code would be appreciated. thanks in advance. Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last used cell in row with blanks inbetween
I'm sorry, i do not think i explained myself very well !!
What i need to do is the following: A B C D E F G H I J bp 20.23 12.32 12.32 12.36 15.23 I need to start in A at "bp" and go to "H" and put in a calculation to add the row up giving me the total of the row = B1:G1. Hopefully this is clearer Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last used cell in row with blanks inbetween
In Excel 12 we must use
Cells(1, Columns.Count).End(xlToLeft).Select I mean that this is working in all Excel versions also Excel 12 with 16,384 columns -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Hi Less Try Range("IV1").End(xlToLeft).Select In Excel 12 we must use Cells(1, Columns.Count).End(xlToLeft).Select -- Regards Ron de Bruin http://www.rondebruin.nl "Les Stout" wrote in message ... Hi, i need to start at cell A1 and find the last used cell in the same row using code. The only problem is that there are sometimes one or two blanks in the row.I know i can use - Selection.End(xlToRight).Select But the problem is that it stops at the blanks too. Any help with some code would be appreciated. thanks in advance. Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last used cell in row with blanks inbetween
Thanks Ron, did you see my correction to this thread ?
Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last used cell in row with blanks inbetween
The trick is to start at IV1 and work backwards:
Function finder(r As Integer) As Integer For i = 256 To 1 Step -1 If (IsEmpty(Cells(r, i))) Then Else finder = i Exit For End If Next End Function To use it, set r=to the row of interest and it will return the number of the first non-empty cell starting from the end! -- Gary''s Student "Les Stout" wrote: Hi, i need to start at cell A1 and find the last used cell in the same row using code. The only problem is that there are sometimes one or two blanks in the row.I know i can use - Selection.End(xlToRight).Select But the problem is that it stops at the blanks too. Any help with some code would be appreciated. thanks in advance. Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last used cell in row with blanks inbetween
Hi, thanks these bits of code work great, i corrected myself in my last
thread. I need to calculate the row total and put the result in the cell found by your code. Thanks and sorry for the wrong explanation. best regards, Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last used cell in row with blanks inbetween
Hi Les
Try this then Dim LastValueCol As Integer LastValueCol = Cells(1, Columns.Count).End(xlToLeft).Column Cells(1, LastValueCol + 1).Formula = "=SUM(R1C2:R1C" & LastValueCol & ")" -- Regards Ron de Bruin http://www.rondebruin.nl "Les Stout" wrote in message ... I'm sorry, i do not think i explained myself very well !! What i need to do is the following: A B C D E F G H I J bp 20.23 12.32 12.32 12.36 15.23 I need to start in A at "bp" and go to "H" and put in a calculation to add the row up giving me the total of the row = B1:G1. Hopefully this is clearer Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last used cell in row with blanks inbetween
That's 100% thanks Ron, just a further question if one had to look for a
row with a "Heading" in it e.g. "BP', it's location could be in any row and then go to the last cell in the row, ignoring any blanks and put in the total as per your script, how would the script change ? Thanks very much for the help. best regards, Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last used cell in row with blanks inbetween
Test this one Les
Sub Find_BP() Dim rng As Range Dim LastValueCol As Integer With Sheets("Sheet1").Range("A:A") Set rng = .Find(What:="BP", _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) End With If Not rng Is Nothing Then With Sheets("Sheet1") LastValueCol = .Cells(rng.Row, Columns.Count).End(xlToLeft).Column .Cells(rng.Row, LastValueCol + 1).Formula = "=SUM(R" & rng.Row & "C2:R" & rng.Row & "C" & LastValueCol & ")" End With Else MsgBox "Nothing found" End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Les Stout" wrote in message ... That's 100% thanks Ron, just a further question if one had to look for a row with a "Heading" in it e.g. "BP', it's location could be in any row and then go to the last cell in the row, ignoring any blanks and put in the total as per your script, how would the script change ? Thanks very much for the help. best regards, Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last used cell in row with blanks inbetween
Dankie Ron, ek sal dit probeer. Lekker naweek.
Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
WHY DOES CLICKING A SECOND CELL SELECT ALL INBETWEEN | Excel Discussion (Misc queries) | |||
find blanks crashes on error | Excel Discussion (Misc queries) | |||
Adding a space inbetween letters and numbers in a cell | Excel Discussion (Misc queries) | |||
Cell link inbetween Asterisks | Excel Worksheet Functions | |||
Using IF function for inbetween cell values | Excel Worksheet Functions |