Delete blank cells within a range

I need to be able to delete blank cells within a range but not the entire row.
i.e. if the data I want to keep is in column 'T' and I need to keep the header in row 1 so the range would be from T2 down and this end cell will change every day.

8455ABC

8455XYZ


8455JKL

When the blank cells are removed I need it to look like this

8455ABC
8455XYZ
8455JKL

But I do not want to delete any data from any other cell in any other column.

Can anyone assist?

Thanks
JagwarmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Hi,

I need to be able to delete blank cells within a range but not the entire row.

Err... you need to remove the contents of cells that are already blank?

Alternatively, do you mean that you wish to remove the gaps between cells within a given range so that all the non-blank values are contiguous (one after the other) at the top of the range?

Thanks for clarifying.

BFN,

fp.
0
JagwarmanAuthor Commented:
Alternatively, do you mean that you wish to remove the gaps between cells within a given range so that all the non-blank values are contiguous (one after the other) at the top of the range?

yes that is correct
0
Saqib Husain, SyedEngineerCommented:
With VBA or without?
0
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

[ fanpages ]IT Services ConsultantCommented:
OK, thanks.

If other columns within the worksheet could be used as an temporary "staging area" then the solution could be formulae-based (with a manual step of copying the result back over the original column).

Where you looking for a Visual Basic for Applications code-based solution though?
0
JagwarmanAuthor Commented:
VBA
0
[ fanpages ]IT Services ConsultantCommented:
*"Where"?  The shame! :)

"Were you looking for a Visual Basic for Applications code-based solution though?"

(Thanks for confirming this)

Would you like the removal of any "gap(s)" to be automatic when a value is removed from column [T], or a routine that you execute on-demand (in an ad hoc manner)?
0
Saqib Husain, SyedEngineerCommented:
Try this macro. You should select one cell in the column before running the macro

Sub collatenb()
    Dim sr As Range
    If Selection.Columns.Count = 1 Then
    Selection.EntireColumn.Insert
    Set sr = Intersect(Selection.Offset(, 1).EntireColumn, ActiveSheet.UsedRange).Offset(1)
    sr.SpecialCells(xlCellTypeConstants).Offset(, -1).Formula = "=row()"
    sr.Offset(, -1).Value = sr.Offset(, -1).Value
    sr.Offset(, -1).Resize(, 2).Sort key1:=sr.Offset(, -1).Cells(1, 1), order1:=xlAscending
    sr.Offset(, -1).EntireColumn.Delete
    End If
End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JagwarmanAuthor Commented:
Thanks ssaqibh it does exactly what I am looking for.
0
[ fanpages ]IT Services ConsultantCommented:
Would you like the removal of any "gap(s)" to be automatic when a value is removed from column [T], or a routine that you execute on-demand (in an ad hoc manner)?

I guess the latter case then.
0
Saqib Husain, SyedEngineerCommented:
You are always welcome fp.
0
[ fanpages ]IT Services ConsultantCommented:
:)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.