| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 123689 | 2012-03-11 19:18:00 | Last populated cell in row (Excel) | Mike (15) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 1264324 | 2012-03-11 19:18:00 | 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. |
Mike (15) | ||
| 1264325 | 2012-03-11 19:44:00 | 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<>"" | dugimodo (138) | ||
| 1264326 | 2012-03-11 19:44:00 | 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 (15) | ||
| 1264327 | 2012-03-11 19:46:00 | 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. |
Mike (15) | ||
| 1 | |||||