Remove blanks from list via VBA
 

I have a dynamic list that can have data added/removed from it by the
user. I have got the "adding" part sorted out okay but am having some
trouble with the "removing" part. Here is an example of what my list
looks like:


Code:
--------------------
Name
-----
Tracy
Bob
Anna
Roger
--------------------


So the list is not in alphetical order. What I want to do is be able to
remove any item from the list e.g. remove "Anna":


Code:
--------------------
Name
-----
Tracy
Bob

Roger
--------------------


And then to move all entries below where "Anna" was in order to remove
the blank space e.g.


Code:
--------------------
Name
-----
Tracy
Bob
Roger
--------------------


However I do -not- want to sort the list alphabetically. I want the
names to stay in the exact same order.

Does anyone have any suggestions as to how I can achieve this via a VBA
macro?

Cheers
-Rob

Ps - I should also add that I don't want to do this by deleting the
rows that have the blank cells. Ideally I'd just like to move all the
data beneath the blank cell up by one.


--
TheRobsterUK


------------------------------------------------------------------------
TheRobsterUK's Profile: http://www.excelforum.com/member.php...fo&userid=9924
View this thread: http://www.excelforum.com/showthread...hreadid=485716

Remove blanks from list via VBA
 

Here is some code

Public Sub MoveData()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow - 1 To 1 Step -1
If Cells(i, "A").Value = "" Then
Cells(i + 1, "A").Resize(iLastRow - i).Cut
Cells(i, "A").Select
ActiveSheet.Paste
End If
Next i
End Sub



--

HTH

RP
(remove nothere from the email address if mailing direct)


"TheRobsterUK"
wrote in message
news:TheRobsterUK.1ym4ay_1132182902.3177@excelforu m-nospam.com...

I have a dynamic list that can have data added/removed from it by the
user. I have got the "adding" part sorted out okay but am having some
trouble with the "removing" part. Here is an example of what my list
looks like:


Code:
--------------------
Name
-----
Tracy
Bob
Anna
Roger
--------------------


So the list is not in alphetical order. What I want to do is be able to
remove any item from the list e.g. remove "Anna":


Code:
--------------------
Name
-----
Tracy
Bob

Roger
--------------------


And then to move all entries below where "Anna" was in order to remove
the blank space e.g.


Code:
--------------------
Name
-----
Tracy
Bob
Roger
--------------------


However I do -not- want to sort the list alphabetically. I want the
names to stay in the exact same order.

Does anyone have any suggestions as to how I can achieve this via a VBA
macro?

Cheers
-Rob

Ps - I should also add that I don't want to do this by deleting the
rows that have the blank cells. Ideally I'd just like to move all the
data beneath the blank cell up by one.


--
TheRobsterUK


------------------------------------------------------------------------
TheRobsterUK's Profile:
http://www.excelforum.com/member.php...fo&userid=9924
View this thread: http://www.excelforum.com/showthread...hreadid=485716

Remove blanks from list via VBA
 

Another approach that might work for you:

Selection.Delete (xlUp)


"TheRobsterUK" wrote in message
news:TheRobsterUK.1ym4ay_1132182902.3177@excelforu m-nospam.com...

I have a dynamic list that can have data added/removed from it by the
user. I have got the "adding" part sorted out okay but am having some
trouble with the "removing" part. Here is an example of what my list
looks like:


Code:
--------------------
Name
-----
Tracy
Bob
Anna
Roger
--------------------


So the list is not in alphetical order. What I want to do is be able to
remove any item from the list e.g. remove "Anna":


Code:
--------------------
Name
-----
Tracy
Bob

Roger
--------------------


And then to move all entries below where "Anna" was in order to remove
the blank space e.g.


Code:
--------------------
Name
-----
Tracy
Bob
Roger
--------------------


However I do -not- want to sort the list alphabetically. I want the
names to stay in the exact same order.

Does anyone have any suggestions as to how I can achieve this via a VBA
macro?

Cheers
-Rob

Ps - I should also add that I don't want to do this by deleting the
rows that have the blank cells. Ideally I'd just like to move all the
data beneath the blank cell up by one.


--
TheRobsterUK


------------------------------------------------------------------------
TheRobsterUK's Profile: http://www.excelforum.com/member.php...fo&userid=9924
View this thread: http://www.excelforum.com/showthread...hreadid=485716

Remove blanks from list via VBA
 

How about...


Sub DelBlanks()

On error resume next
set rng =Range("a1:a" & [a65536].End(xlUp).Row)
rng.SpecialCells(xlCellTypeBlanks).Delete

End sub


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=485716