PDA

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"

snb
12-12-2013, 02:16 AM
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?

snb
12-12-2013, 08:54 AM
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.

snb
12-12-2013, 02:37 PM
Sub M_snb()
[G1:G200]=[if(g1:G200="","","'"&substitute(G1:G200,":",""))]
End Sub