Free Microsoft Excel 2013 Quick Reference

Last entry in column Results

I haven't programmed in 20 years and have lost the chops I did have.

I have a small spread sheet that has a specific column range (D$$) that
needs to be searched for a text code during a SORT to determine which cells
equal the code word "done". Then for each cell with that code, the entire row
that it sits in gets grayed out to make it distinguishable from other cell
entries in column D$ range D$$ that are NOT equal to done.

After 3 sessions of trying to remember how to do this and having read the
helps sections associated with it, I continue to get errors and the formula
does not work.

My last attempt ended with a formula: =If (D$$="done",$6:$17) .

The range shown on the right end is the range of the row numbers to be
grayed out if "done" is found in a column D cell. I DO NOT want the entire
range grayed out - only those rows with cells in column D that ="done".

While I realize that the condition portion of the IF statement (left side
in parens) returns a value of 1 or 0 (true/false) depending on equality to
"done" in each cell, I don't remember if EACH cell address is passed to the
range argument on the right side to be acted upon in the conditional
formatting individually. Apparently not since it doesn't work.

A little help would be kindly appreciated.


i am setting up a stock record spreadsheet on excel. I want to be able to
enter incoming quantities of stock into the same cell on the spreadsheet each
time and have them tally onto the in stock total each time, but without
forgetting what the last value added on was. I know the basics of how to
calculate addition and subtraction formulas, but dont know how to get a
single cell data entry. At present every time i enter a figure, it goes into
the next cell down in the column, and the total cell adds up every cell in
the column to reach a total.

Hi, say I have a list of job numbers down one column on my worksheet and the list is loaded into a combobox on a user form, but some times new job numbers are add to the bottom of the list, is there a way to get the combo box in VBA to look down the list atomically to the last entry, instead of telling it range “a1:a33” ???

Thanks SI

Hi All,

I have a rather complex macro that I just cannot get my head around at all. I am by no means an expert in macro writing and would really appreciate some help.

I attach a sample of my workbook for you to see and hope it helps!

Basically I need to write a macro that go through the worksheet ' ARCHIVE1", detect if there is an entry in both columns B and D and then copy the line that does not appear on worksheet 'Report'. The complex part however is that I need the line to then be pasted into worksheet 'Report' in the relevant section based on the contents on column D. So for example if Column D contains 5151 I would like the new row to be inserted after the last occurance of that number.

In the sample attach on ARCHIVE1 I have highlighted two rows, 13 and 39 as exmaples of what I would like to detect and copy.

I hope someone here is able to help me out


I have a macro in excel that is copying out to another newly created workbook then saving that workbook as a tab delimited (.txt) file.

The issue that I am having is that I am trying to upload this file into another application (MS Dynamics/Great Plains) and it has a large number of characters trailing at the end of the data.

For Example say I have the following in excel in columns and a row

Column1 Column2 Column3
Fred Smith RI
George Jones CA
Carl White VA

When I copy this out I expect that the last character would be the letter A in VA in Column 3. However what I end up with is 40 or so characters after it. Now in the excel worksheet I do have formulas that carry down below the total text in order to ensure that the formula is there for any possible number of rows. Is this the issue?

So I guess my question is: A how do I eliminate these "blank" characters in excel OR how do I do so if the file is in notepad (can I apply an Excel Macro to notepad)

Or, if its my extra formulas; is there a way to copy a formula down only so far as the last row of data. So for example in the data above I have the data displayed in Column 3 is actually from a vlookup that carries down 50 rows (it is told to be "" if column 1 is empty) in case I have 50 rows of data. I could have the formula only in one cell and copy it down just only so far as needed.

Hopefully this makes sense.

Here is the relevant part of the code I am using:

    Application.ScreenUpdating = False 
    Sheets("Entry Prep").Select 
    Sheets("Entry Prep").Select 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
    Sheets("Entry Prep").Select 
    Application.CutCopyMode = False 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
    Sheets("Entry Prep").Select 
    Application.CutCopyMode = False 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
    Sheets("Entry Prep").Select 
    Application.CutCopyMode = False 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
    ActiveWorkbook.SaveAs Filename:= _ 
    "T:AccountingDynamicsUploadEntryLoanSalesEntry.txt", FileFormat:=xlText _ 
    , CreateBackup:=False 
    ActiveWorkbook.Close False 
End Sub[COLOR=#3E3E3E][B][/B][/COLOR] 

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

Hi Guys,

Please I need a vba code to find Invoice No. in a database and replace content of a cell in the same row.

I have a Sales database with 7 columns, as follows:

1. Invoice No. - Column A
2. Invoice Date - Column B
3. Month of Sale - Column C
4. Gross Amount - Column D
5, Vat Amount - Column E
6. Net Amount - Column F
7. Date Paid - Column G

I have created a Multipage Excel Userform for - (1). Sales Data Entry; and (2).Receipts Entry.

I already have a vba code for the Sales Data Entry, and the code works fine. When an invoice is generated, the Sales Data Entry part of the Multipage Userform is used to enter the sales invoice details onto the database. At this stage, what is entered in the last column i.e. Column G is the word 'UNPAID'.

The second page of the Multipage Userform is to be used to enter Receipts and update the database. That page contains only two textboxes, namely - (1) Invoice No. and (2) Date Paid, and a command button named 'Enter Receipt'.

Please I need a macro to run the page 2 of multipage userform (i.e. the 'Receipts' page) - so that when a user enters the Invoice No. and Date Paid, and clicks the 'Enter Receipts' button, the macro will make a search in Column A of the database for the Invoice No. that has been paid. If the Invoice No. is found, then replace the word 'UNPAID' that is entered against the invoice no. in Column G with the Date Paid (in dd/mm/yyyy format).

I need to clarify that each Invoice No. is unique and is never repeated in Column A (i.e. an invoice number cannot appear more than once in column A). The vba code should also be able to provide a message saying 'Invoice No. not found' if the search does not find an invoice no. that is keyed into the 'Receipts' userform (may be in error).

Thanks in advance for your kind help.


Hi Ozgrid,
I would reckon there is a similar addin out there, and I think this is actually a built in function 2007+, but here is my own home grown addin for removing duplicate entries.

It really shouldn't need much tinkering, but it's got decent notes if you want to. It's meant to be a fully encapsulated solution, callable from either the GUI or VBA. The project is unlocked, and I hope someone out there can find some use for it. I use it every day! If you do manage to break it, or have a suggestion, let me know and I'll be happy to update it.

-The key combo Ctl-e performs a sort on the first column of the selected range.
-The first row of any duplicate entries in the first column is preserved, the remaining are discarded. Data outside the selction is not affected.
-Undo is fully supported.
-If called from GUI, has failsafe measures attempt to shrink large selections, or warn the user that the operation may take some time.
-If called from VBA it won't untoggle any application settings (events, calculation, selection, etc)

The code: (Attached as an .xla, but posting the code in case you are wary about opening strange addons )

 '   Stores info about current selection
Type SaveRange 
    Val As Variant 
    Addr As String 
End Type 
Public OldWorkbook As Workbook 
Public OldSheet As Worksheet 
Public OldSelection() As SaveRange 
Option Explicit 
Public Sub DupeSort() 
     'Using a helper sub to make it visible to the macro dialog in the GUI
     'Otherwise with even an optional parameter it won't show up there.
End Sub 
Public Function SortandRemoveDupes(Optional RangeToSort As Range) As Long 
     '      v2.0
     'This function is meant to be called with the keyboard shortcut "CTL-E"
     'It sorts and removes duplicate rows based on the first column of the selection
     'The first of multiple entries is preserved
     'To call from VBA, just pass the optional range reference
     '**Return Value = number of lines removed, -1 for error
     'Known issues
     '-No ability to change sort direction or key another column, must be changed programatically
     '-Formatting is not restored on an undo (if it's sorted)
     'Author: Demgar(Email removed from my forum post, go away bots)
     'This code is open source, feel free to edit or use however you like
     '*But please credit the author
     '4/26/10 -Tre-  Added undo functionality
     '               Inverted the clearing loop - the row with the first result is kept, since it checks from the bottom
     '               Changed the way the first/last row are initialized - should speed execution up considerably for large
     '               Turned on/off application parameters for speed. Calc set back to user's state. The reversal is behind
the error checking
     '8/19/10 -Tre-  Reviewed the clearing loop and changed slightly
     '               added a check to count the selection and dump if < 2
     '               Added a check for multiple
     '               cleaned up some references using with statements
     '8/20/10 -Tre-  Moved to a seperate workbook to publish
     '               Added an optional parameter to enable it to be called from vba
     '               also return value
    Dim FirstCol As Integer, FirstRow As Integer, lastCol As Integer, lastRow As Long 
    Dim i As Long 
    Dim MyRange As Range 
    Dim calcstate As Integer, screenUpdateState As Integer, eventsState As Integer 
    SortandRemoveDupes = -1 
     'Checking to see if we can run it
    If RangeToSort Is Nothing Then 
        If TypeName(Selection)  "Range" Then Exit Function 
        If Selection.Count > 130000 Then 'Arbitrary, but would let us sort 2 entire columns pre 2003 before it's triggered
            Set MyRange = Intersect(Selection.Parent.UsedRange, Selection) 
            If MyRange.Count > 200000 Then 'Again arbitrary
                If MsgBox("Warning: This operation may take some time, would you really like to proceed?", vbExclamation,
"Warning", vbYesNo) _ 
                = vbNo Then Exit Function 
            End If 
            Set MyRange = Selection 
        End If 
        Set MyRange = RangeToSort 
    End If 
     'Turn things off for speed
    With Application 
        calcstate = .Calculation 
        screenUpdateState = .ScreenUpdating 
        eventsState = .EnableEvents 
        .Calculation = xlCalculationManual 
        .ScreenUpdating = False 
        .EnableEvents = False 
    End With 
    With MyRange 
        If .Count < 2 Then Goto Endy 
        If .Areas.Count  1 Then Goto Endy 
        SetUndoPublics MyRange 'define the publics used to undo
        FirstRow = .Row 
        lastRow = .Item(.Count).Row 
        FirstCol = .Column 
        lastCol = .Item(.Count).Column 
        .Sort Key1:=Cells(FirstRow, FirstCol), Order1:=xlAscending, Header:=xlNo, _ 
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
        SortandRemoveDupes = 0 'No longer in "error state" if we get this far
        For i = lastRow To FirstRow + 1 Step -1 'the first row of the selection shouldn't be compared outside the selection.
             'We want to start from the bottom, so we will decrement the loop.
             'Which means a for/each won't easily work.
            If Cells(i, FirstCol) = Cells(i - 1, FirstCol) Then 
                Range(Cells(i, FirstCol), Cells(i, lastCol)).ClearContents 'Could be .clear if you want to kill formatting
                SortandRemoveDupes = SortandRemoveDupes + 1 'Return success count
            End If 
        Next i 
        .Sort Key1:=Cells(FirstRow, FirstCol), Order1:=xlAscending, Header:=xlNo, _ 
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
    End With 
     'Specify the Undo Sub - this must go above the endy, in case we trigger the dump
    Application.OnUndo "Undo the Remove Dupes macro", "UndoRemoveDupes" 
    With Application 
        .Calculation = calcstate 'reset to the user's state
        .ScreenUpdating = screenUpdateState 
        .EnableEvents = eventsState 
    End With 
End Function 
Private Sub SetUndoPublics(MyRange As Range) 
     '8/10 - Not sure why I first set this up to loop the range instead of just saving the range object in memory
     ' It works though, and CBA to fiddle with it
    Redim OldSelection(MyRange.Count) 
    Dim Cell As Range 
    Dim x As Long 
    Set OldSheet = MyRange.Parent 
    Set OldWorkbook = OldSheet.Parent 
    For Each Cell In MyRange 
        x = x + 1 
        OldSelection(x).Addr = Cell.Address 
        OldSelection(x).Val = Cell.Formula 
    Next Cell 
End Sub 
Private Sub UndoRemoveDupes() 
     'Undoes the effect of the remove dupes sub
    Dim x As Long 
    Dim calcstate As Integer, screenUpdateState As Integer, eventsState As Integer 
    With Application 
        calcstate = .Calculation 
        screenUpdateState = .ScreenUpdating 
        eventsState = .EnableEvents 
        .Calculation = xlCalculationManual 
        .ScreenUpdating = False 
        .EnableEvents = False 
    End With 
On Error Goto ErrorHandler: 
     '   Make sure the correct workbook and sheet are active
     ' This could be done without selecting, but we assume the undo
     ' is called from the GUI, so lets show the user what's going on
     'reselect their range
    Range(OldSelection(1).Addr, OldSelection(UBound(OldSelection)).Addr).Select 
     '   Restore the saved information
    For x = 1 To UBound(OldSelection) 
         'Won't restore any formatting that got sorted
        Range(OldSelection(x).Addr).Formula = OldSelection(x).Val 
    Next x 
    With Application 
        .Calculation = calcstate 'reset to the user's state
        .ScreenUpdating = screenUpdateState 
        .EnableEvents = eventsState 
    End With 
    Exit Sub 
     '   Error handler
    MsgBox "Can't undo!" & vbCr & Err.Description 
Resume Endy: 
End Sub 

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

For a Poker Account.

I had requested this forumla before when I was on a PC and it worked fine. I am now on Mac Excel.

See post:

The formula =INDEX(I:I,COUNT(I:I))-I4 does not seem to work on my Mac , =INDEX(I:I,COUNTA(I:I))-I4) does not work either.

See attachment below:
In cell M3, I need a formula that will subtract I4 from Ix to show my profit. (Ix being the last bankroll entry.) So when I enter a new balance the profit is automatically updated.

Picture 1.png



I was wondering if anybody could help with the workbook I am working on.
I have previously had some fantastic help with it but am a little stumped.
It works by agents entering their work totals for each day, on sheet 1. This is then transferred to the individual agents records.
From these agents records I then wish to generate averages.
I think this would be easy enough to do, but to make it easier on the eye, I have made the averages contained in columns F:I into data validation lists. But the problem is that my boxes only show one type of average, instead of the three.
So I think a macro which recognises what type of average is in cell F2:i2, and then changes the formula accordingly depending on the type of average.
For pnc Daily average, the formula would consist of sum of column B / by the number of entries, for conflicts would be column C etc
The pnc weekly average would consist of sum of last 5 entries in column B / by 5, for conflicts would be column C etc
The pnc hourly average would consist of the entry in column B / by the number of hours worked in column O.

Is this possible to do with the data validation list. I hope this makes sense, if anybody has any further questions if this is a bit unclear.

Kind regards



I am struggling badly to create a macro to change cells in the final colum in all worksheets with a sum formula if they already contain a numerical entry. The formula will sum from column c to the second last column (left adjacent) cells in the same row.

Any help would be greatly appreciated.

Can anyone please provide the code used which allows excel to autocopy a formula down a column to the point of the last entry in the preceding column.

Many thanks,

For a while now I have been using some code to determine how far down a
worksheet the last line of data appears. For example:

With ActiveSheet
lngLastRow = .Range("A65536").End(xlUp).Row
End With

Does anyone know the equivalent function to count how far out from the left
(i.e. if there is data in columns A to G) - I'm sure it must exist but
cannot quite figure it out.


if your values are continuous with no blanks in the middle then
will average last 30 values in column A and will give error if you have less
than 30 readings.

"Sick Puppy" <Sick> wrote in message
>I am making a new spreadsheet to record daily blood pressure readings.
> day's readings go into a row below the previous day's readings. I can use
> "=average" to get the average for all entries. Is there a function or
> other
> means of getting the average for, for instance, the last 30 entries, which
> would be for the last 30 days?
> Thanks in advance!
> Tim

I would need some support to create a VBA for the follwoing task. (see my uploaded sample file).
In columns from A to J I enter the data continously in more rows.
Runing the macro need to
- copy the data in columns A-D-C-D (data in each row as far as the last entry) to columns L-M-N-O
- then copy columns A-E-F-G (data in each row as far as the last entry) into column L-M-N-O but after the row contains the last data,
- then copy columns A-H-I-J (data in each row as far as the last entry) into column L-M-N-O but after the row contains the last data,
finaly sorting the columns L-M-N-O in ascending order by columns M,N and O.

hey all,

i have a table that tracks the date and the number of times a student has been contacted in a specific range of dates. the table has dates across the top row and names of students in the first column. every time a student is contacted, a "x" marks the specific box for student and that particular date.

what i wanted to know is if there was a way to find out the last time a specific student was contacted?

the way i thought of breaking it down is to find the last entry in the row, and then find the corresponding date for that specific entry. does this make sense? let me know if i need to clarify anything



I am trying to write a macro to perform the following actin and I am unable to think a way to do this easily. Here is the scenario.

I will have 3 columns with data in the following way.
Name Size $
A 10 20
B 200 45
B 350 48
B 500 52
C 50 26
C 78 25
D 1000 100
E 80 26.33
E 160 35.44
E 250 48.22
E 300 53.55
E 360 60.23
F 5 0.98
F 20 18
G 120 53.23
G 190 68.35
G 250.5 78.25
G 325 98.354

And I would like to get the output in the following way.

Name Size $ Size $ Size $ Size $
A Min 20
B 200 48 550 52
C 50 25
D Min 10
E 80 35.44 240 48.22 490 53.55 790 60.23
F 5 18
G 120 68.35 310 78.25 560.5 98.354

Here is the explanation how I came up with the output. Column A can be used as a primary key. For example if i take row two, it has only one entry with Name "A" so the output should contain Min in Column 2 for that particular row and column C should be 20 only

Next let us take second uniquie value from column A which is "B" and here is the data for that:

B 200 45
B 350 48
B 500 52

The output is, column B should be 200, column C should be 48, column D should be 550(200+350), column E should be 52. Since 52 is the last value we will go to next unique value and we will proceed. And I would like to get the output in horizontal format like shown below.

B 200 48 550 52

Hope I explained clearly, what i intended to be performed using macro.

I have attached a sample book for your reference.
Thank you for your Time in Advance.

Hi All

I am fairly new and the code below might be huge but so far it works.

Private Sub
 Sheets.Add After:=Sheets(Sheets.Count)
 ActiveSheet().Name = TextBox2
 Range("A1") = TextBox1
 Range("B1") = TextBox2
    Dim c
    For Each c In Range("A:A").Cells
        If c = "" Then
         Exit For
        End If
End Sub
Explanation of the code and workbook.......

The workbook consists of basically of a index sheet where only column A & B has info in. Name and serial number. Each of these are hyperlinked to a sheet in the workbook that shows the history of the Name or Serial no.

Then I have a userform where you can either select existing name or serial number or you can add a new Name and Serial no.

Now the last part is where this code comes in......when you add the new name and serial it opens a new sheet ,names it after the serial number and adds the name and serial no. to the index.

But if you re open the workbook and select the last entry then it won't open the history of that client because it is not hyperlinked to the sheet in the workbook.

I hope this helps and that anyone can help.
Thank you

Hello: My experience with VB is very limited. I have managed to fumble around and find most of the bits and pieces of code that have been needed to obtain the desired results. I have been working on this for quite a while now. I play with it here and there and have gotten most of the features working that I wanted.
The entire worksheet is protected and the only way for the users to enter or change any of the entries is via a userform.
Most of the info on the worksheet is calculated by formulas (which took much trial and error to get working)
To make a long story short what I want to do is to Sort the info by date. I have included a command button on the userform to do this, but am having trouble selecting the range that I would like to sort.
All the dates are in column A
I have the following code that works to select the appropriate range of dates in column A
Range(("A7"), Range("A65536").End(xlUp)).Select this selects the entries in column A from A7, which is the first entry, up to and including the last entry
However I want to add columns D,E,G, I & J (Including the same rows as the ones selected for column A) to the selection prior to sorting by column A

Perhaps another way to do this would be to select the entire columns leaving out the headers (I have 6 rows of header information) The only thing is I don't know the format to specify the number of rows of header information. I was thinking that there should be no reason not to select the whole column as every cell with no entry could automatically be sent to the bottom of the list.
Whatever help you can offer would be appreciated. Thanks

Someone fills out a form online and I get an email with these same fields every time. I have to put them into excel.

Name - Tom Smith
Company - 123 Tire Company
Address - 456 Franklin Lane
City - Boston
State - MA
Postcode - 10000
Phone - 555-255-2655
Mobile -
Email -
Message - I want to find out more about your products

The specific format I have in excel is in column format going across.

First Name
Last Name
Home Phone
Cell Phone

How can I automatically extract that data from the email to that format in excel without having to copy and paste every entry?

Thank You

Hey there.

This is slightly long, although only the first part is really my question - the rest is my reasoning for different approaches which don't seem like they'll work... so that's not as important = )

So, I have some comboboxes I plan to use for state, city, and local address on a userform. I want to populate them in such a way that each higher-level category restricts the options for each lower-level entry. Example: If someone chooses the state of CA in the first box, only cities in CA are showing up in the "city" combobox. I know this is fairly straightforward, but how I am trying to implement it is not.

First off, the data I am using has already been set up in a special worksheet. The general format is this:

States are listed across the top in the first row, one state per column.

Cities are listed in the columns beneath the state they belong to. The last entry in each city list is the word "Other" (which can be selected if the city the user wants is not already in the list). Also at the end of each Cities list (after Other) is a blank cell (which might be helpful to use later? anyway...). Example:

Alaska | Alabama | ...

Anchorage | Birmingham
Other | Mobile
| Other

Local addresses are organized in a somewhat similar way below this...

I have the State combobox populating well, but I am having trouble figuring out how to get the Cities combobox working. Something making this more challenging is the fact that the list of Cities is supposed to be dynamic, so the list may grow (but probably not shrink).

Does anyone have any suggestions on getting this working? Here are some ideas I've had, but that don't seem like they'll work out.


Scenario 1:

Use a foreach statement and the value of the state combobox to cycle through the range of states in the States row. Then take that column value and display in the Cities combobox everything in a range from the column below the State until reaching a cell with the value "Other." I can't figure out a way to get the range to stop at a (dynamically ending) cell once it reaches a value of "Other" however. I thought I could use a MATCH, but I think I'd need the row position of Other to define the end of range input for that (the "Lookup_array" value). So I can't find something if I need that something to find it!!

Anyway, in pseudo-code something like this:

Foreach (cell) in (Range of States)
if comboboxState.Value = cell.value
statecolumn = (column value of (the current) cell)
Next cell

Then something like:

(varEndCityRange) = MATCH("Other", (start range, end range), 0)

Foreach (cell) In .Range(.Cells(3, statecolumn), .Cells(varEndCityRange, statecolumn))
comboboxCity.AddItem (cell).Value

Next (cell)

Again, I don't think this will work because I can't figure out how to define the ranges in MATCH without already knowing the info I'm looking for (!!)


Scenario 2:

Once I know the column value for state, the cities start two rows down (so that's a fixed position). I was thinking of using OFFSET to somehow ID the range and spit it into the Cities box, but again I don't know how I could query down the list until I hit "Other" and then stop.


Scenario 3:

I could throw the entire range into an array, query for Other, find its cell position, and try to work from there. I get kind of lost from there though... that seems unnecessarily complex - unless I am missing something (very possible).


Scenario 4:

Make a variable Named Range for each list of cities. I think once this is done all I'd need to do is assign the combobox value to the named range. BIG downside on this is that I think this means I'd have to make a separate range for every state (dozens) AND that I'd probably need to use a similar strategy when I move on to populate the Local Address combobox (so dozens x about another dozen, NOT a great prospect).

And insight into this brain twister would be very well appreciated!!

Thanks = )

No luck finding an answer? You could always try Google.