Last Column With Data

How do I check a certain row to find out the last column in the row that has 
data?
0
Utf
5/5/2010 5:01:01 PM
excel.programming 6508 articles. 2 followers. Follow

6 Replies
1895 Views

Similar Articles

[PageSpeed] 31

Dim myRow as long
dim LastCol as long

myrow = 1243

with worksheets("SomeSheetnamehere")
    lastcol = .cells(myrow,.columns.count).end(xltoleft).column
end with

msgbox LastCol

This will break if you've used the last column in that row -- or if you haven't
used any cells in that row.

Is that a problem?

   

Bishop wrote:
> 
> How do I check a certain row to find out the last column in the row that has
> data?

-- 

Dave Peterson
0
Dave
5/5/2010 5:17:29 PM
This gives a interger value but I can't make it work with this statement:

..Columns("B:" & LastCol).EntireColumn.AutoFit

Do I need to set LastCol as something other than Long?  Or is there a 
different syntax I should use to  make the above statement work?

"Dave Peterson" wrote:

> Dim myRow as long
> dim LastCol as long
> 
> myrow = 1243
> 
> with worksheets("SomeSheetnamehere")
>     lastcol = .cells(myrow,.columns.count).end(xltoleft).column
> end with
> 
> msgbox LastCol
> 
> This will break if you've used the last column in that row -- or if you haven't
> used any cells in that row.
> 
> Is that a problem?
> 
>    
> 
> Bishop wrote:
> > 
> > How do I check a certain row to find out the last column in the row that has
> > data?
> 
> -- 
> 
> Dave Peterson
> .
> 
0
Utf
5/5/2010 7:07:01 PM
Try changing this line...

lastcol = .cells(myrow,.columns.count).end(xltoleft).column

to this...

lastcol = split(.cells(myrow,.columns.count).end(xltoleft).address, "$")(1)

and see if that works for you.

-- 
Rick (MVP - Excel)



"Bishop" <Bishop@discussions.microsoft.com> wrote in message 
news:065D91F9-F491-40EF-8C79-4A82C4936A66@microsoft.com...
> This gives a interger value but I can't make it work with this statement:
>
> .Columns("B:" & LastCol).EntireColumn.AutoFit
>
> Do I need to set LastCol as something other than Long?  Or is there a
> different syntax I should use to  make the above statement work?
>
> "Dave Peterson" wrote:
>
>> Dim myRow as long
>> dim LastCol as long
>>
>> myrow = 1243
>>
>> with worksheets("SomeSheetnamehere")
>>     lastcol = .cells(myrow,.columns.count).end(xltoleft).column
>> end with
>>
>> msgbox LastCol
>>
>> This will break if you've used the last column in that row -- or if you 
>> haven't
>> used any cells in that row.
>>
>> Is that a problem?
>>
>>
>>
>> Bishop wrote:
>> >
>> > How do I check a certain row to find out the last column in the row 
>> > that has
>> > data?
>>
>> -- 
>>
>> Dave Peterson
>> .
>> 
0
Rick
5/5/2010 7:16:27 PM
You could use:

..range("b1",.cells(1,lastcol)).entirecolumn.Autofit
or
..range("b1",.columns(lastcol)).entirecolumn.autofit



Bishop wrote:
> 
> This gives a interger value but I can't make it work with this statement:
> 
> .Columns("B:" & LastCol).EntireColumn.AutoFit
> 
> Do I need to set LastCol as something other than Long?  Or is there a
> different syntax I should use to  make the above statement work?
> 
> "Dave Peterson" wrote:
> 
> > Dim myRow as long
> > dim LastCol as long
> >
> > myrow = 1243
> >
> > with worksheets("SomeSheetnamehere")
> >     lastcol = .cells(myrow,.columns.count).end(xltoleft).column
> > end with
> >
> > msgbox LastCol
> >
> > This will break if you've used the last column in that row -- or if you haven't
> > used any cells in that row.
> >
> > Is that a problem?
> >
> >
> >
> > Bishop wrote:
> > >
> > > How do I check a certain row to find out the last column in the row that has
> > > data?
> >
> > --
> >
> > Dave Peterson
> > .
> >

-- 

Dave Peterson
0
Dave
5/5/2010 7:51:35 PM
Well that worked perfect.  But now I can't figure out what's wrong with this 
statement:

..PageSetup.PrintArea = ("$A$1:" & .Cells(LowerPrintArea, LastCol))

I also tried:

..PageSetup.PrintArea = ("$A$1",  .Cells(LowerPrintArea, LastCol))

and

..PageSetup.PrintArea = "$A$1",  .Cells(LowerPrintArea, LastCol)

but neither of those worked either.  I'm sure I'm just messing up the syntax 
and Excel help was no help.  Can you advise?

"Dave Peterson" wrote:

> You could use:
> 
> ..range("b1",.cells(1,lastcol)).entirecolumn.Autofit
> or
> ..range("b1",.columns(lastcol)).entirecolumn.autofit
> 
> 
> 
> Bishop wrote:
> > 
> > This gives a interger value but I can't make it work with this statement:
> > 
> > .Columns("B:" & LastCol).EntireColumn.AutoFit
> > 
> > Do I need to set LastCol as something other than Long?  Or is there a
> > different syntax I should use to  make the above statement work?
> > 
> > "Dave Peterson" wrote:
> > 
> > > Dim myRow as long
> > > dim LastCol as long
> > >
> > > myrow = 1243
> > >
> > > with worksheets("SomeSheetnamehere")
> > >     lastcol = .cells(myrow,.columns.count).end(xltoleft).column
> > > end with
> > >
> > > msgbox LastCol
> > >
> > > This will break if you've used the last column in that row -- or if you haven't
> > > used any cells in that row.
> > >
> > > Is that a problem?
> > >
> > >
> > >
> > > Bishop wrote:
> > > >
> > > > How do I check a certain row to find out the last column in the row that has
> > > > data?
> > >
> > > --
> > >
> > > Dave Peterson
> > > .
> > >
> 
> -- 
> 
> Dave Peterson
> .
> 
0
Utf
5/5/2010 9:08:01 PM
If you leave off the property you want, then excel will (always???) use .value.

So .Cells(LowerPrintArea, LastCol) would refer to the value in that cell.  If it
looked like an address, it would work -- but probably not the way you want!

But try specifying .address as the property you want:

..PageSetup.PrintArea = "$A$1:" & .Cells(LowerPrintArea, LastCol).Address

or use a range and let excel do the work for you:

..PageSetup.PrintArea = .range("A1", .Cells(LowerPrintArea, LastCol)).Address



Bishop wrote:
> 
> Well that worked perfect.  But now I can't figure out what's wrong with this
> statement:
> 
> .PageSetup.PrintArea = ("$A$1:" & .Cells(LowerPrintArea, LastCol))
> 
> I also tried:
> 
> .PageSetup.PrintArea = ("$A$1",  .Cells(LowerPrintArea, LastCol))
> 
> and
> 
> .PageSetup.PrintArea = "$A$1",  .Cells(LowerPrintArea, LastCol)
> 
> but neither of those worked either.  I'm sure I'm just messing up the syntax
> and Excel help was no help.  Can you advise?
> 
> "Dave Peterson" wrote:
> 
> > You could use:
> >
> > ..range("b1",.cells(1,lastcol)).entirecolumn.Autofit
> > or
> > ..range("b1",.columns(lastcol)).entirecolumn.autofit
> >
> >
> >
> > Bishop wrote:
> > >
> > > This gives a interger value but I can't make it work with this statement:
> > >
> > > .Columns("B:" & LastCol).EntireColumn.AutoFit
> > >
> > > Do I need to set LastCol as something other than Long?  Or is there a
> > > different syntax I should use to  make the above statement work?
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > Dim myRow as long
> > > > dim LastCol as long
> > > >
> > > > myrow = 1243
> > > >
> > > > with worksheets("SomeSheetnamehere")
> > > >     lastcol = .cells(myrow,.columns.count).end(xltoleft).column
> > > > end with
> > > >
> > > > msgbox LastCol
> > > >
> > > > This will break if you've used the last column in that row -- or if you haven't
> > > > used any cells in that row.
> > > >
> > > > Is that a problem?
> > > >
> > > >
> > > >
> > > > Bishop wrote:
> > > > >
> > > > > How do I check a certain row to find out the last column in the row that has
> > > > > data?
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > > .
> > > >
> >
> > --
> >
> > Dave Peterson
> > .
> >

-- 

Dave Peterson
0
Dave
5/6/2010 12:37:17 AM
Reply:

Similar Artilces:

Only two columns allowed to enter data?
Hi, is it possible to have only two columns, say, Column B & E in a worksheet to allow user entering data? How can I do that? Thank you. GL format those cells as unlocked (make sure the rest are locked), and the turn on worksheet protection (tools, protection -- duan ----------------------------------------------------------------------- duane's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1162 View this thread: http://www.excelforum.com/showthread.php?threadid=27595 By default all cells are "locked" when sheet is protected. Select columns ...

Find the last, 2nd last and 3rd last data in a column
I have data listed in several colums and need to create a formula to detail the last (most recent) 2nd last and 3rd last piece of data in the column. I have used the following formula to display the last but cant edit this or create another formula to get the 2nd and 3rd last pieces of data. =LOOKUP(2,1/(A:A<>0),A:A) Thanks in advance Jamie If there are no empty cells in the range: last cell: =INDEX(A:A,COUNTA(A:A)) penultimate: =INDEX(A:A,COUNTA(A:A)-1) semi-penultimate: =INDEX(A:A,COUNTA(A:A)-2) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme r...

Formating text columns layout with page breaks in Excel 97
I was wondering if there is a way to link columns, so once the page break is reached that Excel will automatically continue a list in a designated column to the right? I am trying to create a multi-paged checklist of DVD titles. The sheet is mostly text based, created in Excel 97. The eventual goal is to use this checklist as a template to catalog all other types of media too (CDs, video game carts,...). The spreadsheet is 4 Columns across (A to D) and each page in the spreadsheet is 30 rows in length. Columns 'A' and 'C' will have check-boxes; Columns 'B' and ...

Merger Two Data Sheet
Hi All: I need to merge two variables but I could not find any instructions on this issue. I have a data sheet 1 like this: Course Hours Acc100 3.0 Acc122 3.0 Mar095 3.5 Bus100 4.0 Bus101 3.0 I have data sheet 2 like: Course Instructor Acc100 Charles Acc101 David Acc122 James Mar095 Mary Mar100 Greg Mar155 Sharon Bus100 Lisa Bus101 Sue Bus105 Tom Data sheet 1 and data sheet 2 have the same variable Course. Course contains much more records in data sheet 2 and all records of Course in data sheet 1 are incl...

How can I display worksheet summary page ie title/size/last updat.
I'm running MSExcel 2003. I am often working with large workbooks with mutiple worksheets. It would be v helpful if I could see/sort and print off a summary that shows worksheet properties similar to Explorer eg: workbook :xxxx ---------------- worksheet last update size names 1/2/04 64k companies: 5/2/03 893k etc... any ideas? Jon I wrote this sometime ago and it works as a workbook_open event (Needs to be placed in the ThisWorkbook code module). It could be adapted to work otherwise though. It adds a sheet and lists all the built-in and c...

Adding data (autoincrement) into a xml file with the help of a dataset
Hi, I have a strange problem with my xml file. In the schema of this xml file I have set one column to autoincrement. When I enter the data in my xml file directly, the autoincrement-column gets its correct values. Now I want to add new data with the help of a dataset. I use the following lines of code: Dim ds_functions As New DataSet ds_functions.ReadXml("Test.xml") Dim drCurrent As DataRow drCurrent = ds_functions.Tables(0).NewRow drCurrent(1) = Me.txt_bla.Text ds_functions.Tables(0).Rows.Add(drCurrent) Dim sw As System.IO.StreamWriter = New System.IO.StreamWriter("Test.x...

How long will my capital last
Hi If I had a capital sum of say �100,000 and I was taking an annua income of 5% and I was getting 3.5% interest annually - Can you help m demonstrate how long my capital last? Thank you Dust -- Message posted from http://www.ExcelForum.com Dusty, =3DNPER(3.5%,-5000,100000,0) If, by 5%, you mean 5% of the original investment (=A35.000 each year), = your money will last for approximately 35 years at 3.5% interest. See Excel Help for more info on NPER(). Regards, Anders Silven "Durhamr" <Durhamr.yajun@excelforum-nospam.com> skrev i meddelandet = news:Durhamr.yajun@excelf...

Select all chart data points
I have made a graph in Excel 97, but I cannot select all data points on the graph. When I click on a data point only every other point (or so) is highlighted. I have deleted the graph and re-entered the data, still no good! If I make a quick temorary graph by opening excel from scratch in a new window this seems to work (click on a point and they all highlight). Is there a default setting somewhere!? Thanks What is it you want to do with the series once you select it? "CL" <clairelead@blueyonder.co.uk> wrote in message news:1106737562.533026.13210@c13g2000cwb.googlegroups.co...

how do I make a graph with one very large piece of data
I am trying to make a graph in Excel with mostly small numbers for data, but with one large number. The graph does not display well. Is there a way to break up that large number so that the small numbers show up too. eg Hockey 3 Soccer 2 Basketball 3 Football 59 There is no really "good" way Some suggestions are 1) Put football on a secondary axis 2) Use a log scale 3) Set the maximum y scale value to a small number (5, say) and add a text box to state that football is 59 I favour #3 best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "...

Multi-select listbox question
I have a multi-select listbox and wish to obtain data from the last clicked record in the listbox (you'll notice that if you make multiple selections then only the last-clicked record has a dotted line around it). What, if any, listbox property identifies this record as last-clicked? Thanks Dave -- Posted via a free Usenet account from http://www.teranews.com ...

Graphic Visualization of an ORACLE data on EXCEL
Hi Everone, I am a new on EXCEL. I am using an Excel to display the data from an Oracle database. I am using several excel sheets in order to display various data from an Oracle database. For example: sheet one for displaying the oracle users information, sheet two for displaying oracle files information etc. I am using the following query for displaying the data in one of an excel sheet: QUERY: select username, ''''||sid||','||serial#||'''' sid_serial,status,module, OSUSER "Workstations",MACHINE Hostname,PROGRAM, to_char(logon_time,'D...

mapping columns
Greetings Is there a way to map column number to alphabet such column 6 equates to "F" and column equates to AA. Thanks Not really sure what you want here but perhaps the following will help. The first 2 return the column number from the alpha id and the second 2 return the column alpha id from the column number. Returning the Address (alpha id) the parameters indicate whether to return absolute or relative. ,0 is relative and 1 (or no parameter) returns absolute. Sub test() MsgBox Columns("AA").Column MsgBox Columns("F").Column MsgBox ...

Importing Selected Source Book Data
I have a large source worksheet containing data in various columns. When a value is indicated in the "qty" column of the source book, I need the entire row of information to display and list in a second worksheet. How do I display only the selected information in the second worksheet? Personally, I'd keep the data in one location. You could apply Data|Filter|Autofilter, then filter on greater than 0 to show the values that you want. If you really want this stuff on another worksheet, you could copy those visible rows and paste into a new worksheet. Greyson wrote: > ...

why is my last digit converted to zero?
I'm using Excel 2003 and when I enter a 16-digit number or cut and paste a 16-digit number, the last digit is converted to zero. This does not happen with 15 digits or fewer. Excel's numeric precision is 15 decimal digits. If you need to calculate with the number, you probably won't miss anything. If the number is a key, such as a creditcard number, add an apostroph (single quote, " `") before it; it will not show, but it will cause the number to be treated as text. Another way is to format the cell as text before entering the number -- Kind Regards, Niek Otten ...

Userform to enter data on worksheet
Hi I am new to VBA and userforms so I would greatly appreciate any help. First row in my sheet contains names of months: Jan 2008 forecast, Jan 2008 actual, Feb 2008 forecast, Feb 2008 actuals and so on... Second row contains inflows, third outflows. There are some figures in the forecasted months. I need to create userform to enter actual values of inflows and outflows. Userform would have one combo box to choose appropriate month (on the base of first row) and two textboxes for inflows and outflows, a submit button and cancel button. Again any help would be appreciated. johniellll Is ther...

'no data found' in 28MB pst file?
I used a utility to repair a suspected bad pst file and that was the results, 'no data found'. Is this possible? Yes, this is possible since "white spaces" exist in a pst-file. Can you still open the original file? If so; create a new pst-file and copy the available data into the newly created pst-file. -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Backup and Restore -Create an Office XP CD slipstreamed with Service Pack 3 ----- "Frank" <h@rack.com> wrote in message news:4e5Gc.5757$ne6.1151@nwrdny03.gnilink.net... >I ...

Plotting data across columns
Hi all, I'm pretty sure there is a way to custom define this but I'm very confused. I need to plot based on the following criteria and so far it looks like this: s/n | control s/n | metering taper 1 | metering taper 2 | metering edge break 1 | meb2 | [drain edge taper and break 1 and 2 also] But eventually I'm going to have to take averages and then attempt to plot out. I'm going to need to make 4 graphs as seen here, metering taper and metering edgebreaks as well as the drain edges. Eventually I may need to add more to metering tapers (like a 3rd or 4th catagory) If t...

How do I convert some data in the row to columns?
I have an issue that I need to resolve. I'm currently getting data in a row format, but I need to convert some of the data into columns, for importing into Access. For example...... Item Description Jan 05 Feb 05 Mar 05 Apr 05 ........... ABC123 Widget A 100 250 175 210 ............ XYZ123 Widget B 50 75 100 76 ............ To convert to........ Column1 Column2 Column3 Column4 Item Description Date Qty ABC123 Widget A Jan 05 100 ABC123 Widget A ...

Can one correlate data betw/sheets ?
Hello - I'm new to this group as well. I'm on Excel 2003, Win XP Pro and I'm fairly familiar with the excel interface, but probably still a newbie. I'm doing a running balance, like a checkbook, with deposits and withdrawals etc. I've set that up fine, but I'd like to be able to correlate and continue data between sheets because I've set the worksheet up to have 8 sheets within it (if I'm using the wrong terminology, this is one (1) excel worksheet file with 8 sheets within it - that's 8 sheet tabs at the bottom). In specifics, I'd like to ha...

Trying to find the fourth blank cell in a column
Hello I'm trying to find th fourth blank cell in a column and select a range based on it. Should be simple but for some reason I can't makeit work. Anyone tell me what I'm doing wrong? I'd be most grateful. For background, I have some data followed by two blank cells, followed by more data, folowed by a blank cell, followed by more data followed by another blank cell, followed by more data followed by blank cells to the end. It's the start of these final blanks I'm trying to find programatically. Problem is that after firstqun,the rest of the variables show as ...

Last logged on by
Hello! Using exchange 2003. When I look under Adminstrative groups -> Server -> "name" -> First storage group -> mailbox store -> mailboxes you can se all the mailboxes that you have and last logged on by. Under last logged on by I can se that one person (me) have logged on on to diffrent acconts, even if I havent don it. WHAT is this? Best regards Micke Micke: Did you use your account as the service account for anti-virus or spam? Also, IIRC, even if you view someone's Outlook Calendar, it would mark the mailbox as being logged on by you. Regards...

Pivor Table Data Field
This is a multi-part message in MIME format. ------=_NextPart_000_000C_01C5A644.DE8D0D20 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have tried to "show" a data field after using "hide" in field = settings. How can I make this reappear? Not using Field drop down = option. The fields orgin is from the excel list and not a calculated = fields. ------=_NextPart_000_000C_01C5A644.DE8D0D20 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC ...

Combining data
I'm sure there is a formula out there to do this, but I'm not finding any help in the help function in excel. I have one worksheet for each day of the month with data. I'm looking to take the data from each of those sheets and dump it all into one "summary sheet" instead of having to copy and paste each day (I'm not looking to summarize any of it). If a new sheet is added, I would like to have the data added to the bottom of the "summary sheet" Any suggestions as to how to do this? Hi, Ron has a macro to do that look into http://www.r...

Display index of column headers in column A
A worksheet has data organized in columns that will be used to create drop-down lists. How can I display in column A the contents in the column headers - i.e. row A contents (which are the titles of each list) so that I can see the names of all the column headers without having to scroll across the screen. Note that there are empty columns which will be filled with new lists that will be added at later times, so the formula should ignore columns that have not header (blank A column) and return column headers that contain text, but without creating blank cells in column A that ...

Data Labels on Clustered Column w/ 3D Visual effect #2
Clustered Columns can have Alignment as "Inside End" This doesn't seem possible with 3D Visual effect Is there a way to resolve this? Yes, use 2D charts. They provide a better representation of the values being plotted without the implied false third dimension. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ emwashburn wrote: > Clustered Columns can have Alignment as "Inside End" > > This doesn't seem possible with 3D Visual effect > > Is there a way to ...