Using Macros to change data in a spreadsheet.

[jcborland]

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]

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]

sub M_snb()
   columns(7).specialcells(2).value="NO"
End Sub

[jcborland]

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]

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]

Check the helpfiles in the VBEditor ( Alt- F11 ), F1, lemma 'specialcells'.

[jcborland]

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]

Sub M_snb() 
    [G1:G200]=[if(g1:G200="","","'"&substitute(G1:G200,":",""))] 
End Sub