Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Used Cell in a row??
Hi.
I hope someone could help me this time, too. I need to find the last used cell (that is, not empty, contains some data) in a Row and to get the column number, witch contains that cell. E.G. such rows person1 | 4556 | | 4564 | | | person2 | www | | | | dddddd | So, if I specify a row number 1, the code should return "4", and for the second row - "6". I have tried many examples from Internet, but they return either the first column or number "256"... Any ideas? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Used Cell in a row??
this may do what you want:
Option Explicit Sub test() Dim lastcol As Long Dim ws As Worksheet Set ws = Worksheets("Sheet1") lastcol = ws.Cells(1, 256).End(xlToLeft).Column MsgBox lastcol End Sub -- Gary "Andrius B." wrote in message ... Hi. I hope someone could help me this time, too. I need to find the last used cell (that is, not empty, contains some data) in a Row and to get the column number, witch contains that cell. E.G. such rows person1 | 4556 | | 4564 | | | person2 | www | | | | dddddd | So, if I specify a row number 1, the code should return "4", and for the second row - "6". I have tried many examples from Internet, but they return either the first column or number "256"... Any ideas? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Used Cell in a row??
this is more generic
Option Explicit Sub test() Dim lastcol As Long Dim ws As Worksheet Set ws = Worksheets("Sheet1") lastcol = ws.Cells(1, Columns.Count).End(xlToLeft).Column MsgBox lastcol End Sub -- Gary "Andrius B." wrote in message ... Hi. I hope someone could help me this time, too. I need to find the last used cell (that is, not empty, contains some data) in a Row and to get the column number, witch contains that cell. E.G. such rows person1 | 4556 | | 4564 | | | person2 | www | | | | dddddd | So, if I specify a row number 1, the code should return "4", and for the second row - "6". I have tried many examples from Internet, but they return either the first column or number "256"... Any ideas? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Used Cell in a row??
For row #13, for example:
Sub findit() MsgBox (Cells(13, Columns.Count).End(xlToLeft).Column) End Sub -- Gary''s Student - gsnu2007b "Andrius B." wrote: Hi. I hope someone could help me this time, too. I need to find the last used cell (that is, not empty, contains some data) in a Row and to get the column number, witch contains that cell. E.G. such rows person1 | 4556 | | 4564 | | | person2 | www | | | | dddddd | So, if I specify a row number 1, the code should return "4", and for the second row - "6". I have tried many examples from Internet, but they return either the first column or number "256"... Any ideas? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Used Cell in a row??
OK, that I have understand myself, at what place the desired row number is
to be specified :) Thank You both for the code. "Gary''s Student" wrote in message ... For row #13, for example: Sub findit() MsgBox (Cells(13, Columns.Count).End(xlToLeft).Column) End Sub -- Gary''s Student - gsnu2007b "Andrius B." wrote: Hi. I hope someone could help me this time, too. I need to find the last used cell (that is, not empty, contains some data) in a Row and to get the column number, witch contains that cell. E.G. such rows person1 | 4556 | | 4564 | | | person2 | www | | | | dddddd | So, if I specify a row number 1, the code should return "4", and for the second row - "6". I have tried many examples from Internet, but they return either the first column or number "256"... Any ideas? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Used Cell in a row??
For row #13, for example:
Sub findit() MsgBox (Cells(13, Columns.Count).End(xlToLeft).Column) End Sub OK, that I have understand myself, at what place the desired row number is to be specified :) I rearranged the relevant parts of the posted messages for clarity. Notice that Gary''s Student started by saying "for row #13" and then in his posted code uses 13 as the first argument for the Cells Range object? Just replace the 13 with the row you want to check. If it helps you any, you can turn this code into a function to make your calling it simpler... Function LastColumn(RowNumber As Long) As Long LastColumn = Cells(RowNumber, Columns.Count).End(xlToLeft).Column End Function This way, assuming the function is in scope, you can use it directly in your code. For example LastColumnInRow5 = LastColumn(5) or MsgBox "There are " & LastColumn(5) & " columns being used in row 5." Rick |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Used Cell in a row??
It could be a built-in constant or you could as the User:
Sub findit() Dim n As Long n = Application.InputBox(Prompt:="enter ruw number") MsgBox (Cells(n, Columns.Count).End(xlToLeft).Column) End Sub -- Gary''s Student - gsnu2007b "Andrius B." wrote: OK, that I have understand myself, at what place the desired row number is to be specified :) Thank You both for the code. "Gary''s Student" wrote in message ... For row #13, for example: Sub findit() MsgBox (Cells(13, Columns.Count).End(xlToLeft).Column) End Sub -- Gary''s Student - gsnu2007b "Andrius B." wrote: Hi. I hope someone could help me this time, too. I need to find the last used cell (that is, not empty, contains some data) in a Row and to get the column number, witch contains that cell. E.G. such rows person1 | 4556 | | 4564 | | | person2 | www | | | | dddddd | So, if I specify a row number 1, the code should return "4", and for the second row - "6". I have tried many examples from Internet, but they return either the first column or number "256"... Any ideas? |
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 | |||
NEED VBA TO SELECT A CELL; NOTE THE CELL VALUE;COPYADJ CELL;FIND CELL VALUE IN A RANGE AND SO ON | Excel Programming | |||
How to create/run "cell A equals Cell B put Cell C info in Cell D | Excel Discussion (Misc queries) |