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

[Dennis]

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

 

[Maistrye]

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

 

[Dennis]

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

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

 

[Gord Dibben]

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

 

[Dennis]

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

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

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]

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

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

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

 

[Maistrye]

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

 

[Dennis]

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

 

[Maistrye]

Looks like it.

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

Scott