Help with simple macro.

[mberggren]

Hi all,

I have a simple macro that just copies a row like this below. This one is executed by Ctrl+A on the keyboard.

Sub Macro1()
'
' Macro1 Macro
'
    Selection.Copy
    Selection.Insert Shift:=xlDown
    
End Sub

Cell A is suppose to work like an auto increment number. So for every new row that´s created the number from previous post is increment´s by 1.

Then i copy a row now looks like this.

1
1
1
1

This is what i want.

1
2
3
4

Is this possible to add to this macro ?

// Regards

 

[HaHoBe]

Hi, mberggren,

what shall happen if you add another cell inside that range - what number should they get? This macros rioght now works only for Column A (for rows use .EntireRow.Insert shift:=xlDown)

With ActiveCell
  .Insert shift:=xlDown
  .Offset(-1, 0).Value = .Offset(-2, 0).Value + 1
End With

should do fine as long as you enter the cells at the end. If there is a start point I´d prefer to use filldown or DataSerie sinstead...

Ciao,
Holger

 

[mberggren]

Thx for quick answer, well it didn´t work out the way i wanted it to do. See result in attached file.

default.jpg is showing when nothing has been done
using_macro.jpg is showing result of the code below
wanted_result.jpg is showing what i´m looking for

Sub Macro3()
'
' Macro3 Macro
'
' Keyboard Shortcut: Ctrl+a
'
    Selection.Copy
    Selection.Insert shift:=xlDown
    
    With ActiveCell
    .Insert shift:=xlDown
    .Offset(-1, 0).Value = .Offset(-2, 0).Value + 1
    End With
End Sub

// Regards

 

[mberggren]

I addad this row, it almost works .. problem is that it count´s backwords

Senario 1:

So if i stand on ROW 26 and the number in the active cell is 8001 and presses Ctrl+a three times the result is:

ROW 26, 8003
ROW 27, 8002
ROW 28, 8001

So 8001 jumps down to ROW 28. And the active row is still ROW 26

When i press Ctrl+a the active row should be the once´s that is last copied.

Senario 2:

If i now stand on ROW 27 and presses Ctrl+a one time the result is:

ROW 26, 8003
ROW 27, 8003
ROW 28, 8002
ROW 29, 8001

Can i make this number unique ? If the number is true you pick the next available number in line, in this case it should be 8004

ActiveCell.Value = ActiveCell.Value + 1

Sub Macro3()
'
' Macro3 Macro
'
' Keyboard Shortcut: Ctrl+a
'
    Selection.Copy
    Selection.Insert shift:=xlDown
    ActiveCell.Value = ActiveCell.Value + 1
 
End Sub

// Regards

 

[namliam]

Selection.cells(2,1).select ' MOve the selected cell down one.
ActiveCell.Value = ActiveCell.Cells(0, 1).Value + 1 ' Get the value from the previous row and add one

Hope that helps

 

[mberggren]

Thx,

That did actually work ..

Did some changes to it, works fine now.

Sub Macro3()
'
' Macro3 Macro
'
' Keyboard Shortcut: Ctrl+a
'
    Selection.Copy
    Selection.Insert shift:=xlDown
    Selection.Cells(2, 1).Select
    ActiveCell.Value = ActiveCell.Cells(0, 1).Value + 1
    Application.CutCopyMode = False
    ActiveCell.EntireRow.Select
 
 
End Sub

The next step is to make the numbers in "A" column unique.

Example:
row 26, 8001
row 27, 8002
row 28, 8003

If i set my mouse cursor on row 28 it works fine but if i set my mouse cursor on row 26 for example and press Ctrl + a, it makes a copy as i should, but now i´m having two of 8002. Here i want it to put in the new copied row on row 29 with the new number 8004

// mberggren

 

[namliam]

Is the range / list otherwize completely filled??? I.e. No empty lines anywhere??

You could do something using the Cells function to loop thru all lines. But this is sounding more and more like a Primary key (identifier) you are trying to make. How about sticking this into an Access database and have access do the (autonumber) work for you?

 

[Brianwarnock]

Thx,

The next step is to make the numbers in "A" column unique.

Example:
row 26, 8001
row 27, 8002
row 28, 8003

If i set my mouse cursor on row 28 it works fine but if i set my mouse cursor on row 26 for example and press Ctrl + a, it makes a copy as i should, but now i´m having two of 8002. Here i want it to put in the new copied row on row 29 with the new number 8004

// mberggren

What that says to me is that you want to place the cursor in a row, run the macro to copy that row to a new last row and update the count in colA by 1

Something like

Dim highcount As Long
Dim lngLastRow As Long

With ActiveSheet.UsedRange
lngLastRow = .Cells(1, 1).Row + .Rows.Count - 1
highcount = .Cells(lngLastRow, 1).Value
End With

ActiveCell.EntireRow.Copy
Rows(lngLastRow + 1).PasteSpecial
Cells(lngLastRow + 1, 1).Value = highcount + 1
Application.CutCopyMode = False

Brian

 

[HaHoBe]

Hi, mberggren,

use WorksheetFunction.Max to get the highest value of Column A (may be restricted to only the UsedRange isntead of the whole column like here:

    ActiveCell.Value = WorksheetFunction.Max(Range("A:A")) + 1

Ciao,
Holger

 

[mberggren]

Thx for the answers!

Brian:
It worked just fine

If you have the following scenario:

row 23, 7001
row 24, 7002
row 25, 7003
row 26, 8001
row 27, 8002
row 28, 8003

If you stand on row 7001 and presses Ctrl + a you get 8004 on row 29, is it possible to get the next number in line instead, in this case 7004 after 7003 ?

Like this:

row 23, 7001
row 24, 7002
row 25, 7003
row 26, 7004
row 27, 8001
row 28, 8002
row 29, 8003

// Regards

 

[Brianwarnock]

I'm not sure that this allows the macro to still be classified as simple, but everything is relative.

You are now going to have to loop down column A from the Active cell to the end of the data checking for the Max in the given range, however that is defined, else before running the macro sort on col A and then you can look for the "jump" in the numbers.

Another alternative is to have a blank row between ranges then

LastRowIn Range = ActiveCell.End(xlDown).Row
and the original code, modified to use this, may still work.

Brian

 

[mberggren]

Hi,

Can´t really get this to work as i wanted. Not really sure where to put in this line.

LastRowIn Range = ActiveCell.End(xlDown).Row

Getting error message.


I did create a empty row between the numbers like this.

row 4, 10000
row 5, 10001
row 6, * empty *
row 7, 20000
row 8, 20001
row 9, * empty *

For now i´m using the code below.

ActiveSheet.Unprotect Password:="xxxxx"
Selection.Copy
Selection.Insert Shift:=xlDown
Selection.Cells(2, 1).Select
ActiveCell.Value = ActiveCell.Cells(0, 1).Value + 1
Application.CutCopyMode = False
ActiveCell.EntireRow.Select
ActiveSheet.Protect Password:="xxxxx"

// Regards

 

[namliam]

LastRowInRange = ActiveCell.End(xlDown).Row
FirstRowInRange = ActiveCell.End(xlUP).Row
ActiveCell.Value = WorksheetFunction.Max(Range("A" & FirstRowInRange & ":A" & LastRowInRange)) + 1

Do above instead of only the line below:
ActiveCell.Value = ActiveCell.Cells(0, 1).Value + 1