Free Microsoft Excel 2013 Quick Reference

Keyboard shortcut: last populated cell in row?

Looking for a keyboard shortcut which moves the cursor to the last populated cell in the current row, even when there may be blank cells in the row.

IOW the equivalent of HOME (which moves to the 1st cell in the row, regardless of whether all cells are populated).

CTRL-right arrow is often described as "moving to the last populated cell in the row", but it does not. It moves to the last *contiguous* populated cell in a row, not the ultimate last cell. If any cells are blank it won't move to the last cell in a row.

It seems you frequently need to navigate to the 1st and last cells in a row. The HOME key works for the 1st cell, but I can't find a key for "move to last cell in a row".


Hi folks,

Wonder if anyone can help, i need to find the last populated cell in the row say I9:CN9 and return the description for that column the resides in I5:CN5 and then fill the formula down for about 500 rows, just not sure even where to begin, do i need a custom formula in VBA? as i can't seem to find a combination that works built-in...

All

A spreadsheet I'm doing for work requires me to use the last drawing of fuel from a driver to calculate the MPG for each unit in the fleet.

I was looking a basic LOOKUP formula which checked only one column. However, the spreadsheet has evolved and I now require the LOOKUP formula to check columns and rows (so I6:K37) for the last populated cell.

I amended the LOOKUP formula to contain this and instead of picking up the last populated cell in J17 for example, the formula returns the value 0 as K37 is blank.

This is the only thing the spreadsheet needs to be finished and any help would be greatly appreciated!

Thanks

Hi,

I am attempting to write code which defines a range as from a cell in a column down to the last populated cell in that column. My current code is:

Dim range2 as Range
With Sheets("Grading")
Set range2 = .Range("a2", .Cells(A, Rows.Count).End(xlDown))
End With

I receive an error message stating that the application or object is not defined. I have also tried

End(xlUp)

And received the same error (my intention here is for the range to include up to the lowest populated cell in the column; it is my understanding that xlDown should suffice here).

Any help would be appreciated. Thanks.

I would like to have an Excel formula that will return the last populated cell in a range that is >0. For example, in column E, I have a range for data from E1:E20 I would like to have a formula in cell E25 for example, that would return the last number populated in the range of E1:20 that is greater than 0. If data is entered in cells E1 to E15, I would like the formula to return the data in E15 if that is the last number entered in the range. Thanks.

Hi,
Can someone tell me how I can obtain the value of the last populated cell in a specific column please?

Many thanks

Chris

Hello!

I have data in a single row array. The data is not contiguous such that
there are holes in the data. Several cells may be blank in the first few
cells, in the middle cells, or in the last cells. I need to identify which
cell is the one where the data begins. In addition, I need to identify which
cell is the one where the data ends. It is not necessary to locate "holes"
in the data set. I would like to use functions only to identify these first
and last cells.

Thank you very much!!

Tom

Display a message box which displays cell address of the last populated cell in a row and jumps to 'it' when user clicks “ yes “ button

I am a complete novice to VBA. Is there a cleaner way to write some VBA code performing the following function?

When macro is run:

Displays popup message box with text.Runs string of VBA code which returns cell address of last populated cell in column "A" and displays it as follows: "Last Populated Cell Is: [Cell Address] Jump To The Last Cell?"Gives user chose of "Yes" or "No"
If user choose YES = Jumps to last populated cell.If user choose NO = macro ends and pop box disappears.
Here is my code at present:


	VB:
	
 
 
Sub MsgBox_LastPopulatedCell() 
     
    Dim result As VbMsgBoxResult 
     
    result = MsgBox("Last Populated Cell Is:" & Chr(32) & Range("A65536").End(xlUp).Address & vbCrLf & "Jump To The Last
Cell?", vbYesNo + vbQuestion, "Joshua Excel Tools" & Chr(32) & Chr(169)) 
     
    Select Case result 
         
    Case 6 
        Range("A65536").End(xlUp).Select 
    Case 7 
        ActiveCell.Range("A1").Select 
    End Select 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I'm trying to find
1) the last and
2) first values in a time series of data in which the the data points are scattered all over the place
AND I'd also like to
3) get the corresponding year (Heading name) for both the
3.1) last and the
3.2) first data point

I've found solutions for all but 3.2. Thus the question remains:

How could I lookup the heading description for the first value on a row?

example:

row #; lookup; year1; year2; year3; year4;
1; year3; ; ; 23; ;
2; year2; ; 21; ; 53;
3; year1; 12; ; 45; 2;

In case it helps anyone looking for same information or providing help to solve my problem 3.2 the other solutions I've found are:

1) lookup last value in row

i) http://www.ozgrid.com/forum/showthread.php?t=78242
=LOOKUP(9.99999999999999E+307,R5:AI5)
... also an additional VB solution

ii) http://www.ozgrid.com/forum/showthread.php?t=65233
=INDEX(M12:M500;MATCH(9.99999999999999E+307;M12:M500))
... page also includes solution for 2), below

iii) http://www.ozgrid.com/forum/showthread.php?t=66598
... 2 VB solutions

2) Lookup first value in row
http://www.ozgrid.com/forum/showthread.php?t=65233

=INDEX(M12:500,MATCH(TRUE,INDEX(M12:M5000,0,0),0))
... page also includes solution for 1), above

and

3) Lookup heading of last populated cell in row
http://www.ozgrid.com/forum/showthread.php?t=69525

=LOOKUP(2,1/(I9:CN9""),I$5:CN$5)

4) Could I e.g. modify the above to lookup the year of first value?

Thanks!
Jaakkoh

Hi Everyone,

I have a quick question that I'm sure is pretty easy, but I'm having a problem getting it. Ok here's the scenario...I need to find what the value of the last populated cell of a certain column is. The cell will always be different, but the column will alway remain the same. For example, the column will always be "K" and I just need to find out what the value of the last populated cell is in that column. Thanks for any help!

-beveritt

What im trying to do is take a list that is populated vertically and display it in another worksheet horizontally but in a certain range. Ive made an example here. I want to be able to copy the names and information of people with information (skip names w/o info) and paste there info the first one in G2:I2, next J2:L2, and the last in M2:O2 by pushing a macro button. The outer limits of the range it must fall between then is G2:O2, there are 3 spots and there will never be more than 3 names with information out of a larger group. It would then work to where if you had another list of names you could push the button again and the next info would fall in the row below the previous ones. There are also blank rows between the names which cannot be removed.

For example, first button push the 3 names and info go to G2:I2, J2:L2, M2:O2, push it again this time theres only two names with info and they go to G3:I3, J3:L3, next time you push it you might have three again and they go to G4:I4, J4:L4, M4:O4...you get the picture...has to fall in the range and there is writing on the left and right side of the range so you cant do last filled cell in row to keep placing them to the right.

i dont know how to get started except that need the next available cell in column G code like this to populate down


	VB:
	
Range("G65536").End(xlUp).Select 
ActiveCell.Offset(1, 0).Select 
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
:=False, Transpose:=False 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
any help would be greatly appreaciated in helping me start the code for this

denkinge

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

I'm trying to have a VLOOKUP go to the last active cell in column A. Below is the code I thought would work. I also used examples from http://www.rondebruin.nl/copy1.htm without any sucess. Any ideas would be great!!

Sub vlookuptest()
Dim sourceRange As Range
Dim destrange As Range
Dim Lr As Long

ActiveCell.FormulaR1C1 = "=VLOOKUP(C[-1],xSirensEyeListFrm.xls!C1:C2,2,FALSE)"
Set sourceRange = .Range("A2:A" & .Range("A" & Rows.Count).End(xlUp).Row)
Range("B:B").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A2").Select
Application.CutCopyMode = False
End With
destrange.Value = sourceRange.Value
End Sub

I want a macro to be able to select & highlight (ready for copy/paste) all cells in the column range "A2" to the last populated cell in column A.

Any ideas?

The code below gives me the row number containing the last filled cell in column A.
How would the equivalent code look like to obtain the column number of the last filled cell in row 1?



Hello,

I am trying to figure out how I can always save the value of the last populated cell into
another cell. ie. cell X will always have the value of the last populated cell in it.

I then want to be able to compare that value (last populated cell value) with
the value entered into the next cell.

thank you

John

Is it possible to find the final populated cell in say Column B accross all worksheets in a workbook and have the value in that cell returned to a specified cell?

I have been using this formula

=LOOKUP(2,1/(B6:B200<>""),B$6:B$200)

to find the last populated cell in a column of a worksheet, i've used it in 12 sheets of a workbook. I have it return the value in said cell to cell i1 of each worksheet.

I am now trying to have Excel return the value in the last populated i1 cell in the workbook to a designated cell in a 13th worksheet, but I can't figure out how. Is this possible?

This is driving me mad, would someone please help me to toggle between cell A1 and the last populated cell in column A?
Something like the code below but in a macro that allows me to toggle on one button.

Sub PageStart()
    Range("A1").Activate
End Sub

Sub PageEnd()
Range("A1").End(xlDown.Activate
End Sub


Hi I Hope you can help with this one?

I am trying to do a lookup that displays the value of the last populated cell as long as the two cells in the same row match a certain criteria

ie I want to match criteria aaaaa and bbbbbbbbbd to generate the last populated cell ie "icccccccccd" or match aaaaa and bbbbbbbbbbe to display "ccccccccca"

Row criteria1 Critera 2 info1 info2
1 aaaaa bbbbbbba ccccccccca cccccccccbs
2 aaaaa bbbbbbbbc ccccccccca ccccccccca
3 aaaaa bbbbbbbbd ccccccccca icccccccccd
4 aaaaa bbbbbbbbbbe ccccccccca

I need to keep a summary of our latest prices plus the previous price ,
so on sheet1 cell C5 is the previous price while cell D5 is the current
price.

I want these prices to update automatically whenever a price change
occures. Each time a price change occures the new price is added to row
16, currently the last price is X16 while the previous price is W16.

How do I get cell D5 to lookup the last item in row 16 and C5 to find
the last but one item in row 16.

With thanks

Bob

Hi all,
been a while since I've been on this site.

I have a little problem - as you probably guessed.
I have a spreadsheet in which i need to find the value of the last used cell in a row.

e.g spread sheet uses columns "a" to "l" and rows "1" to "75".
Over time rows are filled in with text ("tom" "dick" "harry") from a to b to c, the most recent being to the right but the rows can move at different paces.

I want to count how many many times each value has come up most recently.

Please can someone help?

Hi!

Can anyone give pointers to find the last used cell in a particular row using a macro? The row doesn't have to be the last used one on the sheet.

TIA n Cheers!
Tanvi

I have a row of numbers in let's say row 2. Each day another number is entered in row 2 in the next column.
I want a formula taht woud simply find the last cell in row 2 that has a number in it.
Any ideas

Genii,

I need a way to have a cell at the end of a row that shows the % change
of the last two cells (with data in them) in a row. Imagine if you had
sales figures in a row, one column for each month. I need a column on
the right with the % change between the last two cells in that row -
like "Sales was up 21.6% from March to April". This formula has to
automagically adjust, only giving the % change opf the last two cells
in the row with data in them.

Does that make sense? Is it possible?

How is that done?

--
chewmanfoo
------------------------------------------------------------------------
chewmanfoo's Profile: http://www.excelforum.com/member.php...o&userid=27112
View this thread: http://www.excelforum.com/showthread...hreadid=466251