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 ***

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 ***

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 ***

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 ***

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 ***

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 ***

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 ***

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 ***

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 ***

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 ***

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 ***