How to count blank cells (Rows) between Non-Blank cells in Col "A"

Discussion in 'Microsoft Excel Misc' started by Dennis, Jul 20, 2006.

  1. Dennis

    Dennis Guest

    Using 2003

    Assume that Column A has a UsedRange(Rows) of 1000 cells.

    Of those cells, only 22 are data-filled.

    What is the best way in VBA to compute the Address and Row Number of
    the first cell in "A" that has a value? (Assume "A7")

    What is the best way in VBA to compute the Address and Row Number of
    the Second cell in "A" that has a value? (Assume "A40")

    What is the best way in VBA to compute the Address and Row Number of
    the (Other cells in "A" that have values? etc.

    Once I have the above then I know that Cell A8 through Cell A39 are
    blank which rows I would like to delete.

    The challenge is there are about 21 other blank-cell ranges in the

    I would like a VBA Loop to delete each of the blank ranges:
    The Loop needs to know the Address to start and the number of rows
    to delete;
    Which means I also need to reset the loop's counter variable with
    the number of rows [like A39 - A7 +1 equals 34] each time a group of
    rows are deleted.

    What is the smartest way to:
    1) start the loop just after value 1 in Column A and
    2) "process" through to Value 2 in Column A Then
    3) reset the Loop Variables so that the loop starts at Value 2 in
    Column A and proceeds to Value 3 in Column A, etc, etc?


    Dennis, Jul 20, 2006
    1. Advertisements

  2. Dennis

    Maistrye Guest

    I may be looking at this wrong, but wouldn't it be better to loop
    starting at the 1000th row and stepping back through to row 1? Then,
    just delete each row that doesn't have a value in column A and proceed
    to the next row. If you go from 1 to 1000, it seems to me there are
    more complications, which is why it seems that going backwards would be

    Maistrye, Jul 20, 2006
    1. Advertisements

  3. Dennis

    Dennis Guest

    You have a good point!

    I tried to keep my need simple.

    The worksheets were prepared by new users who chose to enter text under
    a Column heading, in many rows vs. just one cell using Alt-Enter for
    paragraphing in the cell.

    So, before I delete the extra cell-rows, I must concatenate the
    multiple-cell information into the cell where there is information in
    Column "A".

    If this can be done by going in reverse fine! I just did not think of

    Dennis, Jul 20, 2006
  4. Dennis

    Gord Dibben Guest

    May be easier to just find the blank rows and delete them.

    Public Sub DeleteRowOnCell()
    ''delete any row that has a blank in selected column(s)
    Set coltocheck = Application.InputBox(prompt:= _
    "Select A Column", Type:=8)
    End Sub

    Gord Dibben MS Excel MVP
    Gord Dibben, Jul 20, 2006
  5. Dennis

    Dennis Guest


    You may have seen my other responses in this thread.

    I agree that deleting the empty-cell-in-"A" rows is simple, but in
    reality, I need to concatenate values between the data
    non-empty-cell-in-"A" rows (in other columns) before the delete.

    Therefore, the Loop needs to know what and how many cells to
    concatenate in each column at each non-empty Col "A" point(s). (Column
    A sets the row, but Col B thru Col xx contains the cells to receive the
    concatenated value.

    Hope I did not confuse you.


    Dennis, Jul 20, 2006
  6. Dennis

    Dennis Guest

    Maybe a recap would be helpful.

    Whether the VBA macro starts from the bottom>up or up>down the
    following processing needs to take place.

    The key rows are those Rows with values in Column A. The w/s has
    multiple columns. The macro needs to concatenate multiple cells in
    each column so that all information in the respective rows under each
    column heading gets dropped into i.e. E22 (realizing A22 has a value).

    I am not sure how to organize the concatenation formula variables and
    counter resets to do the job.

    Any help or guides would be greatly appreciated.


    Dennis, Jul 20, 2006
  7. Dennis

    Maistrye Guest

    If I understand this correctly, you want something like this:

    For i = 1000 to 2 step -1
    if (A1 is blank) then
    For j = 2 to NumberColumns
    Append cell in row i, column j to the end of the cell in row i-1
    column j
    Next j
    Delete the row
    End if
    Next i

    Delete row 1 if necessary
    Maistrye, Jul 20, 2006
  8. Dennis

    Dennis Guest

    How simple in concept. It just may work.

    For i = 1000 to 2 step -1
    If (A1 is blank) then
    For J = 2 to NumberColumns(UsedRange) step 1 (default)
    ..... Concatenate going backwards
    Next J
    Delete Row
    End if
    Next i

    Maistrye, did I get the concept?

    Thanks Dennis
    Dennis, Jul 20, 2006
  9. Dennis

    Maistrye Guest

    Looks like it.

    But you'll know when you go to try and use it. :)

    Maistrye, Jul 20, 2006
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.