Determining what cells are blank and copying data into them

  • I have a spreadsheet with names in column A, categories in column B, and data for those categories in other columns. If a person has data for multiple categories, their name is listed only once on the top row, and the other rows in column A are blank. For each person there is a bottom row with combined data for all categories - for those rows, column a shows the name followed by "Total" - for example, "John Smith Total".

    Column B has the name of a category populated, except for the "total" rows for each person. On those rows, Column B is blank.

    Not everybody has data in the same number of categories. Some people have one, most have two, some have three. Right now the maximum number of categories is three.

    I want to create a macro that would copy each person's name into the blank rows in column A (so a name would be listed on each row), to make it easier to run sorting/lookup/filtering on the data in this sheet. It's not necessary to remove the word "Total" from any of the rows in column A. It seems like the way to go about this is roughly:

    Start with cell A2 (which always has a name)

    Move down one cell

    If this cell is blank, check to see if the same cell in column B is blank

    If the cell in column B is blank, copy the data from the cell above into this one

    Move down to the next cell and repeat

    I'm only a beginner with VBA, and I'm not sure how to check whether other cells are blank in relation to the selected cell. I'm also not sure how to have the macro figure out when to stop - maybe checking to see if the whole row is blank. Any suggestions you have would be very welcome.

    Thanks in advance,

  • It would be a lot easier with a representative file to look at if you want feedback.

    With respect to identifying blank cells you normally do something like:

    1. If sheet1.cells(1,1).value = vbnullstring then do something

    for working out when to stop you can define a range then do something like:

    1. For each cell in myrange
    2. do something
    3. next cell
  • Currently there are 86 rows of data. That number could change by a few each week. I was thinking along the lines of having the macro determine somehow that it reached the end of the data (like, if the entire row is blank), but to Justin's point maybe the solution is to define a range. I might be able to figure out how to define the range dynamically, in the meantime if I have to edit the macro to adjust the number of rows each week that is still a big time-saver over what I'm doing now.

    Attached is a modified sample file showing the desired end result for column A.

    Excel Sample File.xlsx

  • I think this will be the fastest way. the code does not Loop to find blanks, but first locates all empty cells in column A using Excel's Specialcells then loops each blank cell which should reduce the running time