I have collected tally chart data for the measured length of a number of the same part.

For example, I have 92 tally marks for 56.11mm, 75 tally marks for 56.12mm, 63 tally marks for 56.13mm and so on.

In order to analyse the data I wish to have a row in excel for each measurement. So for the above example I would have 92 rows with 56.11, 75 rows with 56.12 etc.

How can I quickly enter this data into excel in the format I require?

Thanks in advance.


The Name Box may be your best bet here - it's the box in the upper left of your worksheet, between the column headers and the ribbon, which shows the currently active cell address:

enter image description here

Click in there and replace "A1" with "A1:A92" and hit enter. This will select the appropriate rows. Then, without clicking anywhere else, type in the measurement you want and hit Ctrl+Enter. The value will be placed in all the highlight cells. You can repeat this for as many sets as you need.

Next option would be this macro:

Sub InsertCount()
    Dim varInsert As Variant
    Dim lngCount As Long
    Dim rngInsert As Range

    varInsert = InputBox("What value do you want to repeat?")
    lngCount = InputBox("How many times?")

    If Len(varInsert) > 0 And _
        lngCount > 0 Then
        If Range("A" & Rows.Count).End(xlUp).Row = 1 Then
            Set rngInsert = Range("A1", "A" & lngCount)
            Set rngInsert = Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Resize(lngCount, 1)
        End If
        rngInsert.Value = varInsert
    End If
End Sub

It will ask you what value you want to insert, then how many times to insert it, then put that value into the first free row of column A and repeat it as many times as necessary.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.