View Full Version : Using Macros to change data in a spreadsheet.
jcborland
12-11-2013, 07:40 AM
Hi,
I want to use a macros to change the contents of a column for every active row. For example: My spreadsheet has 20 rows 1 - 20 and I want to put the word "NO" into all 20 rows of column G in sheet 1 and I want to use a macro to do it. I can do this but I end up with a hard coded 1 to 20 so if I was to add 10 more rows to my spreadsheet they would not get updated. Can anyone tell me how I could do this?
Any help would be great.
Regards,
Jim.
Bob Phillips
12-12-2013, 02:05 AM
Assuming you key column is column A, and we are starting in row 1
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("G1").Resize(lastrow).Value = "NO"
sub M_snb()
columns(7).specialcells(2).value="NO"
End Sub
jcborland
12-12-2013, 08:29 AM
Assuming you key column is column A, and we are starting in row 1
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("G1").Resize(lastrow).Value = "NO"
That works thanks. Is there a book to explain any of this stuff? I would never have come up with that.
Is that Geordie Best you've got as your picture? I'm from his neck of the woods N.Ireland.
Thanks again.
jcborland
12-12-2013, 08:31 AM
sub M_snb()
columns(7).specialcells(2).value="NO"
End Sub
Thanks also that works too. I can understand that columns(7) is G but how does specialcells(2) work?
Check the helpfiles in the VBEditor ( Alt- F11 ), F1, lemma 'specialcells'.
jcborland
12-12-2013, 10:57 AM
Hi,
I'm trying to remove :'s from text in a column and having no success. So I want to turn 12:34:56 into 123456. I can use substitute but I end up with both columns being required and that causes other problems. Any ideas?
Thanks,
Jim.
Sub M_snb()
[G1:G200]=[if(g1:G200="","","'"&substitute(G1:G200,":",""))]
End Sub
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.