Macro to Find Last Row in a Column and Add Letter

[Michael151]

Hello all,

Trying to write a macro that will add the letter "X" in the first blank row of a column.

In the column marked "Goods" (header in row 1), the macro should find the first empty row and add an X. For example:

Before:

Goods
data1
data2
data3
data4

After:

Goods
data1
data2
data3
data4
X

"Goods" can be in any column letter, which is why I'd like to use the header in row 1 to identify. Thanks!

 

[Airfix9]

Sub Test

dim C as integer, R as long 'C for column, R for row

with thisworkbook.sheets("Sheet1") 'edit to suit

c = .rows(1).find("Goods", , xlvalues, xlwhole).column

r = .cells(1048576, c).end(xlup).row + 1 'use 65536 if you are in XL03 or before

.cells(r,c).value = "X"

end with

End Sub

 

[Michael151]

Thanks Airfix9, that worked great!

Was also wondering if there was a way to write a reverse of this - that is, if the macro finds an "X" in the "Goods" column, then delete that row.

For example:

Before:

Goods
title1
title2
title3
X

After:

Goods
title1
title2
title3

The macro would delete the row with the X in it.

Thanks for your help!

 

[Airfix9]

Do you want to delete the row? Or just remove the X?

Sub Test

dim C as integer, R as long 'C for column, R for row

with thisworkbook.sheets("Sheet1") 'edit to suit

c = .rows(1).find("Goods", , xlvalues, xlwhole).column

r = columns(c).find("X", , xlvalues, xlwhole).row

' now you have a choice:

' this clears the X

.cells(r,c).clearcontents

' this deletes the row

.rows(r).entirerow.delete

' Delete whichever line you don't want

end with

End Sub

 

[Michael151]

Thanks Airfix9 - that worked!!