Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a small table from a large table by skipping empty columns
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a small table from a large table by skipping empty columns
It looks like you're just sliding everything to the left -- eliminating those
empty cells. If that's true, try recording the macro when you select the range to fix (it won't hurt if you include too many rows!) Hit F5 (edit|goto) Click special check Blanks and hit ok Now just the empty cells should be selected. Edit|delete|shift cells left ======== But are you sure you want to do this??? You'll be losing the nice layout (each field in its own column). If you don't want to destroy the original data, Create a new worksheet copy the data over from the old worksheet (as values) and do that routine 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a small table from a large table by skipping empty columns
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a small table from a large table by skipping empty colu
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a small table from a large table by skipping empty colu
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I create one column (stacked) from a large table of data.. | Excel Worksheet Functions | |||
grouping large pivot table columns | Excel Programming | |||
grouping large pivot table columns | Excel Programming | |||
Macro to create pivot table from large data file | Excel Programming | |||
How can I create a table of contents(worksheets) for a large work. | Excel Discussion (Misc queries) |