Insert sequential number for each row with a record

Question

I'm very new to VBA and learning through code I find on the internet, and also using macros to see code.

I have an imported xls with three columns of data. I have code that does the following:

Inserts a new column A

Deletes column B

Delete rows with no data

Inserts two columns

So far - okay. What I am then trying to do is insert a number starting at 1 in column A1 and sequentially filling in until all rows with records have a number. I used a macro to see the code, but the range will vary (i.e. there are not always 52 rows in my import).

Is there a way to make this dynamic by only applying a number where there is data in the row (Column B will always have data)?

Thanks in advance - all help greatly appreciated!

Sub DeleteBlankRows()


Dim x As Long
Dim lastRow As Long
Dim A As Long


' INSERT A NEW COLUMN A FOR NUMERICAL SEQUENCE

Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

'DELETE ALL BLANK ROWS

With ActiveSheet

   For x = .Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1
       If WorksheetFunction.CountA(.Rows(x)) = 0 Then
        ActiveSheet.Rows(x).Delete
    End If
Next

End With

'add two new columns for population


ActiveCell.Offset(0, 2).Columns("A:A").EntireColumn.Select
Selection.Delete Shift:=xlToLeft
ActiveCell.Columns("A:B").EntireColumn.Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.NumberFormat = "@"



'code to enter a sequential number starting at 1 for every row that has a record

 ActiveSheet.Range("A1").Select

ActiveCell.Select
ActiveCell.FormulaR1C1 = "1"
ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A52"), Type:= _
    xlFillSeries
ActiveCell.Range("A1:A52").Select
ActiveWindow.SmallScroll Down:=15

End Sub

score 0 · Answer 1 · answered Oct 15 '20 at 16:32

0

Could you try this?

'code to enter a sequential number starting at 1 for every row that has a record
'remove your code from here on and substitute with the following

With ActiveSheet
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

ActiveSheet.Range("A1").Select

With ActiveCell
    .FormulaR1C1 = "1"
    .AutoFill Destination:=ActiveCell.Range("A1:A" & LastRow), Type:=xlFillSeries
End With

answered Oct 15 '20 at 16:32

Wils Mils

613
4
9

Thanks Wils - I will check this out later when I am back in the office. – Mark Tennant Oct 16 '20 at 08:39

score 0 · Accepted Answer · answered Oct 15 '20 at 17:07

There are a lot of stuff to improve ~~your~~ my code, but this should get you started

Some things to begin:

Use option explicit at the top of your modules so you don't have unexpected behavior with undefined variables
Always indent your code (see www.rubberduckvba.com a free tool that helps you with that)
Try to separate your logic defining variables and the reusing them
Name your variables to something meaningful and easy to unterstand (avoid x or r)
Write the code steps in plain English first, then develop it in VBA
Check the code's comments, and adapt it to fit your needs

Code

Public Sub PrepareFormat()
    
    ' Set a target sheet
    Dim targetSheet As Worksheet
    Set targetSheet = ActiveSheet ' This could be always the same sheet. If so, replace activesheet with thisworkbook.Sheets("NameOfTheSheet")
    
    ' Insert a new column for numerical sequence
    targetSheet.Columns("A:A").Insert shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
    ' Delete all blank rows
    Dim counter As Long
    With targetSheet
        For counter = .Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1
            If WorksheetFunction.CountA(.Rows(counter)) = 0 Then
                .Rows(counter).Delete
            End If
        Next counter
    End With
    
    ' Add two new columns for population (this next lines would make column B empty, so filling sequentally would not work below
    'targetSheet.Columns("D:D").Delete shift:=xlToLeft
    'targetSheet.Columns("A:B").Insert shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    'targetSheet.Columns("A:B").CurrentRegion.NumberFormat = "@" -> commented this line because cells are going to be empty. What do you want to format as text? maybe this could go after you add the numbers. Also formatting the whole column is a waste of resources
    
    ' Insert a number starting at 1 in column A1 (added number 2 to fill down in sequence)
    targetSheet.Range("A1").Value = 1
    targetSheet.Range("A2").Value = 2
    
    ' Sequentially fill in until all rows with records have a number (this doesn't take into account if there are gaps in column b)
    Dim referenceRange As Range
    Set referenceRange = targetSheet.Range("B1:B" & targetSheet.Range("B" & targetSheet.Rows.Count).End(xlUp).Row)
    targetSheet.Range("A1:A2").AutoFill Destination:=referenceRange.Offset(0, -1)
    
End Sub

Let me know if it works

PS. Check Sidar's answer on how to properly delete empty rows: https://stackoverflow.com/a/9379968/1521579

Just in case it worked, remember to mark the answer, so others can find it. — Ricardo Diaz, Oct 16 '20 at 11:13

Insert sequential number for each row with a record

2 Answers2