PDA

View Full Version : Last populated cell in row (Excel)



Mike
12-03-2012, 08:18 AM
Hi,

I am writing a vba macro in Excel which steps through each row in the sheet, and I need it to find the last populated cell in each row (or in other words the last column with a value in that particular row).

Not every row contains the same number of populated cells - some rows may have 49 values, others 52, others 40 - and I can't guarantee that every cell is populated until the last cell (there may be gaps, but there shouldn't be...)

For each row, how do I find the cell number for the very last populated cell? I don't need the value, I just need to know which cell contains the last value.

Does this make sense? There are currently >50000 rows and as many as maybe 55 columns in the worksheet.

Cheers,
Mike.

dugimodo
12-03-2012, 08:44 AM
I should probably stay out of it as writing vba macros is a bit beyond my current excel expertise, but if you are stepping through the cells anyway couldn't you just start with a simple if statement to check if the cell is blank and halt the macro or generate an output? something along the lines of if A1<>""

Mike
12-03-2012, 08:44 AM
I think I've solved it. This appears to work

Range("IV1").End(xlToLeft).Select

This selects the last populated cell in the row. I can then find the column number by using

ActiveCell.Column

Cheers,
Mike.

Mike
12-03-2012, 08:46 AM
I should probably stay out of it as writing vba macros is a bit beyond my current excel expertise, but if you are stepping through the cells anyway couldn't you just start with a simple if statement to check if the cell is blank and halt the macro or generate an output? something along the lines of if A1<>""Not quite - I'm stepping through the rows, not the cells. I'm using the rows as a whole, but just needed to know how many cells were used in each row. The solution I've posted though seems to do what I want :)

Cheers,
Mike.