Prev: Formula to format selective part in a text
Next: Excel Macro Problem
From: EmB on 23 Apr 2010 09:54
I guess I didn't realize something in my "table"....the "blank" cells aren't
really blank. I think they are "", which might not be the same, since when I
try to select blanks, they are not selected. However, I am able to filter on
"blanks" for each column, highlight all and hit "delete" on the keyboard, and
then you suggestion works. I might try to make a macro for this.

Thank you so much about the "select blnaks" - very handy Excel tip!

"Dave Peterson" wrote:

> ps.
>
> Option Explicit
> Sub testme()
> Dim Wks As Worksheet
> Dim NewWks As Worksheet
>
> Set Wks = Worksheets("Sheet1")
> Set NewWks = Worksheets.Add
>
> Wks.Cells.Copy
> NewWks.Range("A1").PasteSpecial Paste:=xlPasteValues
>
> On Error Resume Next 'just in case there are no empty cells
> NewWks.Cells.SpecialCells(xlCellTypeBlanks).Delete shift:=xlShiftToLeft
> On Error GoTo 0
>
> End Sub
>
>
>
>
> EmB wrote:
> >
> > I have a question I am trying to solve. I have a table that, for many rows,
> > does not have every column filled in. For example, the table would look like
> > this:
> >
> > First Last Color Age Fruit Vegatable
> > John Smith Blue 15 Celery
> > Jane Doe 50 Orange
> > Jim Red Carrot
> > Jones 90 Apple Pepper
> >
> > and I want it to look like this:
> > John Smith Blue 15 Celery
> > Jane Doe 50 Orange
> > Jim Red Carrot
> > Jones 90 Apple Pepper
> >
> > Basically, if a certain column is "empty" a specific row, I want to skip it
> > and paste the next filled in value next to a previous filled in value. Is
> > there any way to do this in a Macro? I'd like to read from my "old" table to
> > create a "new" table.
> >
> > The solution does not have to be elegant, and I can hard code in start rows
> > and end rows.
>
> --
>
> Dave Peterson
> .
>
From: Dave Peterson on 23 Apr 2010 09:58
When I want to clean up this detritus, I do this:

Select the range (ctrl-a a few times to select all the cells)
Edit|Replace
what: (leave blank)
with: $$$$$
replace all

Immediately followed by:
Edit|Replace
what: $$$$$
with: (leave blank)
replace all

In code you could do something like:

Option Explicit
Sub testme()
With ActiveSheet
With .cells 'or a specific range: With .Range("D:D")
.Replace what:="", replacement:="$$$$$", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
.Replace what:="$$$$$", replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
End With
End With
End Sub

====
You could add that kind of code after the .pastespecial line, but before the "on
error" line.

EmB wrote:
>
> I guess I didn't realize something in my "table"....the "blank" cells aren't
> really blank. I think they are "", which might not be the same, since when I
> try to select blanks, they are not selected. However, I am able to filter on
> "blanks" for each column, highlight all and hit "delete" on the keyboard, and
> then you suggestion works. I might try to make a macro for this.
>
> Thank you so much about the "select blnaks" - very handy Excel tip!
>
> "Dave Peterson" wrote:
>
> > ps.
> >
> > Option Explicit
> > Sub testme()
> > Dim Wks As Worksheet
> > Dim NewWks As Worksheet
> >
> > Set Wks = Worksheets("Sheet1")
> > Set NewWks = Worksheets.Add
> >
> > Wks.Cells.Copy
> > NewWks.Range("A1").PasteSpecial Paste:=xlPasteValues
> >
> > On Error Resume Next 'just in case there are no empty cells
> > NewWks.Cells.SpecialCells(xlCellTypeBlanks).Delete shift:=xlShiftToLeft
> > On Error GoTo 0
> >
> > End Sub
> >
> >
> >
> >
> > EmB wrote:
> > >
> > > I have a question I am trying to solve. I have a table that, for many rows,
> > > does not have every column filled in. For example, the table would look like
> > > this:
> > >
> > > First Last Color Age Fruit Vegatable
> > > John Smith Blue 15 Celery
> > > Jane Doe 50 Orange
> > > Jim Red Carrot
> > > Jones 90 Apple Pepper
> > >
> > > and I want it to look like this:
> > > John Smith Blue 15 Celery
> > > Jane Doe 50 Orange
> > > Jim Red Carrot
> > > Jones 90 Apple Pepper
> > >
> > > Basically, if a certain column is "empty" a specific row, I want to skip it
> > > and paste the next filled in value next to a previous filled in value. Is
> > > there any way to do this in a Macro? I'd like to read from my "old" table to
> > > create a "new" table.
> > >
> > > The solution does not have to be elegant, and I can hard code in start rows
> > > and end rows.
> >
> > --
> >
> > Dave Peterson
> > .
> >

--

Dave Peterson
 | 
Pages: 1
Prev: Formula to format selective part in a text
Next: Excel Macro Problem