ADD A ONE TO COLUMN

Oceano

Board Regular
Joined
Feb 13, 2005
Messages
96
Hi,

I wish to add a "1" to the last cell in column A.
EG: By clicking a "button" Column A will have a continous string of 1's.
In this case clicking my button will cause cell 6- will display a 1. ETC.

col A
1-1
2-1
3-1
4-1
5-1
6-


I'm using the number one to activate a function.

O
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Seti

Well-known Member
Joined
May 19, 2002
Messages
2,916
Try this macro which goes in a standard module and assumes you have nothing in column A below the last 1.

Code:
Sub AddOne()
Range("A65536").End(xlUp).Offset(1, 0).Value = 1
End Sub
 

Oceano

Board Regular
Joined
Feb 13, 2005
Messages
96
Thanks Seti,

Works Fine. I know next to nothing about VBA so can you tell me
what to put in the code if the button is on sheet1 but the destination column is in sheet2?

O
 

Seti

Well-known Member
Joined
May 19, 2002
Messages
2,916
Try this code instead:

Code:
Sub AddOne()
Sheets("sheet2").Range("A65536").End(xlUp).Offset(1, 0).Value = 1
End Sub

You need to make sure that you spell the sheet name correctly including all spaces or you'll get an error.
 

Oceano

Board Regular
Joined
Feb 13, 2005
Messages
96

ADVERTISEMENT

Hi Again,

Good stuff, one more request Seti, could the code be modified so I could actaully back up also. In other words toggle adding 1's and taking them away once again?

O
 

Seti

Well-known Member
Joined
May 19, 2002
Messages
2,916
Here you go. Assign this to the delete button.

Code:
Sub DeleteOne()
Sheets("sheet2").Range("A65536").End(xlUp).Delete
End Sub
 

Oceano

Board Regular
Joined
Feb 13, 2005
Messages
96

ADVERTISEMENT

Seti,
I don't know why, when I run this delete macro it actually takes a value from the next column B and inserts it in Column A at proper row location.

O
 

Seti

Well-known Member
Joined
May 19, 2002
Messages
2,916
Sorry about that. Try this slight modification instead.

Code:
Sub DeleteOne()
Sheets("sheet2").Range("A65536").End(xlUp).ClearContents
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,492
Messages
5,601,994
Members
414,490
Latest member
Rip181

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top