[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 77
  • Last Modified:

Excel automatically numbering column

I have a worksheet where I am manually adding rows in a column. The amount of rows in the column could change each time. On one worksheet it can be 1100 on another worksheet the number can be 5,000.  I would like to collectively group those rows in the coiumn no matter how many in groups of 30 and automatically name them in excel. Currently I am doing the following


groupbatch1
groupbatch1
groupbatch1
....this is repeated 30times
groupbatch2
groupbatch2
groupbatch2
groupbatch2
....this is repeated 30times
groupbatch3
groupbatch3
groupbatch3

I would like to see if this can be automated in excel
0
techdrive
Asked:
techdrive
  • 3
  • 2
1 Solution
 
Glenn RayExcel VBA DeveloperCommented:
Yes, using VBA/macros, this could be automated.  Try this subroutine:
Option Explicit
Sub Add_Group()
    Dim strGroupName As String
    Dim lngLastRow As Long
    strGroupName = InputBox("Enter Group Name", "Group Name")
    If strGroupName <> "" Then
       lngLastRow = Range("A" & Cells.Rows.Count).End(xlUp).Offset(1, 0).Row
       Range(Cells(lngLastRow, 1), Cells(lngLastRow + 29, 1)) = strGroupName
    End If
End Sub

Open in new window


This adds/appends 30 identical values in column A.  If you need it in another column:
* change the "A" in line 7 and
* change line 8 - from 1 (A) to the number of the column to add (ex., B - 2, C - 3, etc.)

By the way, this only works in groups of 30.  To expand this, I'd need to know the syntax of how you want your groupbatch labels listed.  For example, would it be batchname-n, where n is an incrementing number that you specify.

Regards,
-Glenn
0
 
techdriveAuthor Commented:
Glenn thanks for the code. I would like to append after batchname-n the actual date. So if I could get some every 30 lines with batchname-10-5-2014-1 and then 2, then 3 and so on. By the way it places output in cell A however its not changing every 30 lines /.
0
 
Glenn RayExcel VBA DeveloperCommented:
Okay, this code will create groups of 30 batchnames with a date and numeric suffix added to differentiate.  The user will be required to enter two values:
*  BatchName
*  Number of groups (used for the suffix value)
Option Explicit
Sub Add_Group()
    Dim strGroupName As String
    Dim lngLastRow As Long
    Dim x, intGroups As Integer
    strGroupName = InputBox("Enter Group Name", "Group Name")
    If strGroupName = "" Then Exit Sub
    intGroups = InputBox("Enter Number of Groups", "Group Quantity")
    If intGroups < 1 Then Exit Sub
    For x = 1 To intGroups
        If strGroupName <> "" Then
           lngLastRow = Range("A" & Cells.Rows.Count).End(xlUp).Offset(1, 0).Row
           Range(Cells(lngLastRow, 1), Cells(lngLastRow + 29, 1)) = _
               strGroupName & "-" & Format(Now(), "m-d-yyyy") & "-" & x
        End If
    Next x
End Sub

Open in new window


Again, it places it in column A, starting at row 2.

Regards,
-Glenn
0
 
techdriveAuthor Commented:
awesome thanks it works
0
 
Glenn RayExcel VBA DeveloperCommented:
You're welcome.
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now