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
    UsedRange.

    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?

    Thanks

    Dennis
     
    Dennis, Jul 20, 2006
    #1
    1. Advertisements

  2. Dennis

    Maistrye Guest

    Dennis Wrote:
    > 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
    > UsedRange.
    >
    > 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?
    >
    > Thanks
    >
    > Dennis


    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
    better.

    Scott


    --
    Maistrye
    ------------------------------------------------------------------------
    Maistrye's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36078
    View this thread: http://www.excelforum.com/showthread.php?threadid=563416
     
    Maistrye, Jul 20, 2006
    #2
    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
    it.

    Thanks

    Maistrye wrote:
    > Dennis Wrote:
    > > 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
    > > UsedRange.
    > >
    > > 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?
    > >
    > > Thanks
    > >
    > > Dennis

    >
    > 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
    > better.
    >
    > Scott
    >
    >
    > --
    > Maistrye
    > ------------------------------------------------------------------------
    > Maistrye's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36078
    > View this thread: http://www.excelforum.com/showthread.php?threadid=563416
     
    Dennis, Jul 20, 2006
    #3
  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)
    coltocheck.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    ActiveSheet.UsedRange
    End Sub


    Gord Dibben MS Excel MVP


    On 20 Jul 2006 10:44:23 -0700, "Dennis" <> wrote:

    >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
    >UsedRange.
    >
    >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?
    >
    >Thanks
    >
    >Dennis
     
    Gord Dibben, Jul 20, 2006
    #4
  5. Dennis

    Dennis Guest

    Gord,

    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

    Gord Dibben wrote:
    > 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)
    > coltocheck.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    > ActiveSheet.UsedRange
    > End Sub
    >
    >
    > Gord Dibben MS Excel MVP
    >
    >
    > On 20 Jul 2006 10:44:23 -0700, "Dennis" <> wrote:
    >
    > >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
    > >UsedRange.
    > >
    > >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?
    > >
    > >Thanks
    > >
    > >Dennis
     
    Dennis, Jul 20, 2006
    #5
  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

    Gord Dibben wrote:
    > 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)
    > coltocheck.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    > ActiveSheet.UsedRange
    > End Sub
    >
    >
    > Gord Dibben MS Excel MVP
    >
    >
    > On 20 Jul 2006 10:44:23 -0700, "Dennis" <> wrote:
    >
    > >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
    > >UsedRange.
    > >
    > >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?
    > >
    > >Thanks
    > >
    > >Dennis
     
    Dennis, Jul 20, 2006
    #6
  7. Dennis

    Maistrye 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 unde

    each
    > column heading gets dropped into i.e. E22 (realizing A22 has

    value).
    >
    > I am not sure how to organize the concatenation formula variable

    and
    > counter resets to do the job.
    >
    > Any help or guides would be greatly appreciated.
    >
    > Dennis


    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
    ------

    Hope the pseudocode makes sense.

    Scot

    --
    Maistry
    -----------------------------------------------------------------------
    Maistrye's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3607
    View this thread: http://www.excelforum.com/showthread.php?threadid=56341
     
    Maistrye, Jul 20, 2006
    #7
  8. Dennis

    Dennis Guest

    Maistrye <> wrote:


    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




    >
    >> 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

    >
    >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
    >------
    >
    >Hope the pseudocode makes sense.
    >
    >Scott
     
    Dennis, Jul 20, 2006
    #8
  9. Dennis

    Maistrye Guest

    Maistrye, Jul 20, 2006
    #9
    1. Advertisements

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.
Similar Threads
  1. Tina
    Replies:
    3
    Views:
    367
  2. SidBord

    Converting Col Number to Col Letter

    SidBord, May 23, 2004, in forum: Microsoft Excel Misc
    Replies:
    1
    Views:
    328
    Debra Dalgleish
    May 23, 2004
  3. Guest
    Replies:
    1
    Views:
    308
    Chip Pearson
    Aug 22, 2005
  4. mikeburg
    Replies:
    3
    Views:
    338
    mikeburg
    Feb 5, 2006
  5. Guest

    Adding values from 1 col based on value in diff col

    Guest, Oct 16, 2006, in forum: Microsoft Excel Misc
    Replies:
    2
    Views:
    274
    Bob Phillips
    Oct 16, 2006
  6. Gayle
    Replies:
    3
    Views:
    270
    Gayle
    May 30, 2008
  7. aquaflow
    Replies:
    3
    Views:
    369
    aquaflow
    Jul 10, 2008
  8. Stacy
    Replies:
    6
    Views:
    359
    T. Valko
    Dec 30, 2008
Loading...