Moving data to blank column??????

Hi

I have a worksheet with various columns of data.

Some of the information takes up 5 columns and some only 
takes up 4 columns.  What I would like to do is run a 
macro to move data from the cells in column 4 into column 
5 where column 5 is blank.

Could I have some help on this please.  

Many thanks

Malcolm Davidson


0
malycom1 (9)
9/19/2003 10:19:28 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
329 Views

Similar Articles

[PageSpeed] 22

Sub Malcolms_blank_cells()
    Range ("e2").select
    do until activecell.offset(0,-1).value = ""
        If activecell.value="" then
            activecell.value=activecell.offset(0,-1).value
        end if
        selection.offset(1,0).select
    Loop
end sub

Steve
"Malcolm" <malycom@hotmail.com> wrote in message
news:0d6501c37e97$81d92900$a401280a@phx.gbl...
> Hi
>
> I have a worksheet with various columns of data.
>
> Some of the information takes up 5 columns and some only
> takes up 4 columns.  What I would like to do is run a
> macro to move data from the cells in column 4 into column
> 5 where column 5 is blank.
>
> Could I have some help on this please.
>
> Many thanks
>
> Malcolm Davidson
>
>


0
9/19/2003 12:16:49 PM
You can accomplish this task without using a macro.

Assuming your data is in columns A-E.
Use the following the formula in F1.
=IF(LEN(E1)=0,"",D1)
Use the following the formula in G1.
=IF(LEN(E1)=0,D1,E1)
Copy down these formulae and you should see the properly 
oriented data in columns F & G.
Hardcode columns F & G (that is, Copy/PasteSpecialValues).
You then can delete columns D & E and the data should be 
properly oriented.

Below is a macro solution.
(I suggest you backup your data first before running this 
macro so you can see the "before" version.)
Selected the data range for your column 5.
Then run the following macro.

Sub ShiftData()
    For irow = ActiveCell.Row To Selection.Rows.Count
        If Len(Cells(irow, ActiveCell.Column)) = 0 Then
            Cells(irow, ActiveCell.Column) = Cells(irow, 
ActiveCell.Column - 1)
            Cells(irow, ActiveCell.Column - 1).Clear
        End If
    Next irow
End Sub

>-----Original Message-----
>Hi
>
>I have a worksheet with various columns of data.
>
>Some of the information takes up 5 columns and some only 
>takes up 4 columns.  What I would like to do is run a 
>macro to move data from the cells in column 4 into column 
>5 where column 5 is blank.
>
>Could I have some help on this please.  
>
>Many thanks
>
>Malcolm Davidson
>
>
>.
>
0
homer_ene (3)
9/19/2003 12:45:32 PM
Select Col 4 as a whole and insert a new column, so that your data is now in columns 5 and 6 with
4 being empty.  Select all of Col 6 and do Edit / Go To / Special / Blank Cells, then do Edit /
Delete / Move cells left.

Assumes you have no data of any consequence past Col 6.

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
                   Sys Spec - Win XP Pro /  XL2K & XLXP

----------------------------------------------------------------------------
  Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



"Malcolm" <malycom@hotmail.com> wrote in message news:0d6501c37e97$81d92900$a401280a@phx.gbl...
> Hi
>
> I have a worksheet with various columns of data.
>
> Some of the information takes up 5 columns and some only
> takes up 4 columns.  What I would like to do is run a
> macro to move data from the cells in column 4 into column
> 5 where column 5 is blank.
>
> Could I have some help on this please.
>
> Many thanks
>
> Malcolm Davidson
>
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.518 / Virus Database: 316 - Release Date: 11/09/2003


0
ken.wright (2489)
9/19/2003 9:56:59 PM
Reply:

Similar Artilces:

if cell is text move left one column
ColB is a long list with sections names followed by category codes I need to move the text into colA leaving colB with codes only (all numbers) ColB. Doors 940590 555998 447006 447008 810697 810705 810706 810707 Windows 619435 525691 525692 Try Sub Macro1() Dim lngRow As Long For lngRow = 1 To Cells(Rows.Count, "B").End(xlUp).Row If Not IsNumeric(Range("B" & lngRow)) Then Range("A" & lngRow).Value = Range("B" & lngRow).Text Range("B" & lngRow).Value = "" End If Next End Sub -- Jacob ...

100% stacked column chart shows wrong information
I wonder if someone can help me. I have created a table that tracks the profitability of a particular project and I’m using the table as a source for a 100% Stacked Column graph. The source data for the graph is in three rows: • B21:M21 - contain the names of the months from Nov – Oct • B22:M22 - money taken in • B24:M24 - profit B23:M23 contains the money paid to supplier each month. I need this in the table but I don’t need to show it in the graph. I just want to show profit as a proportion of what we bill to customers on that project. My problem is that the graph seems to show in...

Can SUMPRODUCT be used for entire column?
This formula results in a numeric result: =SUMPRODUCT((A1:A20)*(B1:B20="b")) but this formula results in a #NUM! result: =SUMPRODUCT((A:A)*(B:B="b")) Which means I need to specify the length of the columns, which may grow over time. Any way to do this for the entire column, without having to specify the length of the column? XL07 removed the limitation on array formulas (which SUMPRODUCT is, even though it doesn't require CTRL-SHIFT-ENTER) and entire columns. For pre-XL07, one can use =SUMPRODUCT(A1:A65535, --(B1:B65536="b")) to get all but on...

SBS 2003 moving of users files
I run SBS 2003 and due to the amount of data on the users drive it has become chokers and have installed a new 1tb drive to keep up with demand for space. I need to move all the data to the new drive but unsure of the process. Is there an easy way of doing this? As it needs to be done asap Thanks -- JimmyJames ------------------------------------------------------------------------ JimmyJames's Profile: http://forums.techarena.in/members/255792.htm View this thread: http://forums.techarena.in/small-business-server/1357051.htm http://forums.techarena.in You c...

Can the data in a chart table be right justified?
Ecxel 2003 and previous versions of the product center the data in the data columns. Can the data in the columns of a chart table be right justified? In article <DABF738B-6C0D-458B-B082-FA9BD8F126A7@microsoft.com>, =?Utf- 8?B?c2FtIGVhZ2xl?= <sam eagle@discussions.microsoft.com> says... > Ecxel 2003 and previous versions of the product center the data in the data > columns. Can the data in the columns of a chart table be right justified? > Have you tried to format the table? If yes, and you haven't been successful it is probably because XL allows very limited cust...

Data migration - Adventure Works
Hiya... I have a company where the adventure works db has been used and had a lot of data populated into the system. We have now purchased MSCRM and have obtained the company reg keys. What is the easiest way to get the data from the 1 system to the next? We will be establishing a new AD domain and users for the new system.... Data Migration Framework? Redeployment Framework? ;) redeploment tools http://www.microsoft.com/downloads/details.aspx?FamilyID=bfced393-61db-49af-9a50-4a90b311fa7d&DisplayLang=en -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "funboy...

Sorting on Column headers
I have a spreedsheet that someone else created and I need to take the Column headers, which are in date form, and rearrange them in chronological order. Can this be done without cutting and pasting? I really need to save time with this project if possible. Thanks!:) --- Message posted from http://www.ExcelForum.com/ > I have a spreedsheet that someone else created and I need to take the > Column headers, which are in date form, and rearrange them in > chronological order. Can this be done without cutting and pasting? I > really need to save time with this project if possib...

Moving Exchange #5
I am needing to move my Exchange server off of the SBS box that it is currently on and move it to another, new server. I cannot keep the servers the same name as I need the old server to continue to run SQL. Are there white papers on how to do this? TIA Scott T. On Fri, 18 Aug 2006 08:43:40 -0700, scottdog129 <scottdog129@discussions.microsoft.com> wrote: >I am needing to move my Exchange server off of the SBS box that it is >currently on and move it to another, new server. I cannot keep the servers >the same name as I need the old server to continue to run SQL. Are...

Label a chart of counts with other percentage data
Here's the data: Group 2005 2006 2007 LTM Data A 19.4% 22.8% 21.2% 19.9% Profitability A 6 7 7 7 Count B 9.5% 31.6% 30.4% 30.7% Profitability B 2 3 3 3 Count C 22.4% 23.6% 16.6% 17.6% Profitability C 15 16 17 18 Count D 19.2% 20.5% 15.9% 13.7% Profitability D 8 8 9 10 Count I have successfully generated a stacked bar chart that shows the counts per group by year. Now I would like to include a label for each group to show profitability for each group in each year in the 4 stacks. How would I do that? Thanks, --...

Creating a chart based on the data in an embedded worksheet
Hi, I have a worksheet with several embedded worksheets. I would like to create a chart based on the data of one of the embedded worksheets without putting the chart in the embedded worksheet. I have tried unsuccessfully to do this. I just wondered if anyone knew how to do it. Thanks, JK JK - You're embedding worksheets within worksheets? Why? Why not just insert the worksheets in line with the main worksheet? To open or edit the embedded worksheet, the parent Excel has to open another instance of Excel, and the chart on the outside of this other instance will never be able to acce...

Opening Multiple Web Links in a Column
Hi, I am very new to using web links in excel. A task I do very often is open a list of different websites that are in various columns in an excel spreadsheet. I am quite sure I am doing it the slowest way possible, so I need some help please. Basically I have been clicking on one link at a time. When I do this, the first website opens and excel automatically minimizes, then I have to go re maximize excel and then click the next web link and the same thing happens, etc... very time consuming. I am wondering if there is a way, either through Excel or whatever means necessary, to open all...

Customize columns in 'Marketing List Members'
I can't figure out where one can customize the columns used within the "marketing list" entitry when you click the 'marketing list members' on the left side to show the members. I want to add some columns, like Email. Screenshot: http://i355.photobucket.com/albums/r469/canadaka_bucket/marketing_list_members.jpg Just read the Posting on the Microsoft CRM Team Blog. <canadaka@gmail.com> schrieb im Newsbeitrag news:306584c6-2043-4962-b12a-d0b9287684bb@b31g2000prb.googlegroups.com... > I can't figure out where one can customize the columns used within the >...

Character Length in a column
I have a column where every cell must have 14 characters. Lets say have the name Angel. I need 9 more charcters. Next cell down have the word cake. I need 10 more charcters.How do I automatciall insert the spaces I need to have 14 charcters. HELP PLEASE!!!!! GOIN NUTS ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements One way: Do you care about what characters are added? I'l...

Freeze the side column/top row & scroll others
what is the function to set (lock in or freeze) the first column and / or top row of a spreadsheet, so the words and numbers remain in the same place as you scroll the other columns and rows. (so you can add more columns..yet keep the main information in the first column/row) Freeze Panes..... In older versions of Excel, it is under Window. In 2007 version of Excel, it is under View. You first select a cell, then activate the command. Excel uses the selected cell's upper left corner to define the freeze point. Play with it. You can also Unfreeze panes that were fro...

Zero to appear as blank
I have a VLOOKUP formula below. When the value is zero, I want the result to be blank. What do I do. =IF(A10="","",VLOOKUP(A10,detail,2)) Thnx Prakash..... =if(a10="","",if(vlookup(a10,detail,2)=0,"",vlookup(a10,detail,2))) Prakash wrote: > > I have a VLOOKUP formula below. When the value is zero, I want the result to be blank. What do I do. > > =IF(A10="","",VLOOKUP(A10,detail,2)) > > Thnx > Prakash..... -- Dave Peterson ec35720@msn.com In addition to modifying your formula as Dave suggeste...

Data validation list from another worksheet?
Is it possible that the value list for data validation be populated fro another worksheet? Puneet Aror -- puneetarora_1 ----------------------------------------------------------------------- puneetarora_12's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1840 View this thread: http://www.excelforum.com/showthread.php?threadid=38572 Sure is! Use a named range as described here: http://www.officearticles.com/excel/drop-down_using_data_validation_in_microsoft_excel.htm ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "punee...

"external data sources" and "external data ranges"
what is the difference between these? i have run the vb macro code on http://support.microsoft.com/kb/330383 to check if i have any external data sources or data ranges but there are none. the reason i need to know is becuase i'm working with office sharepoint server 2007 and i cannot access a file thru the web access web part because it says: "The workbook that you selected cannot be loaded because it contains the following features that are not supported by Excel Services: External data ranges (also called query tables)" anyone? thanks ...

SQL Statement trying to call column not specified in statement
I'm having some trouble with a sql statement within my sub. For some reason, it wants to reference CLPROPSTRTNMBER when I specifically asked for CLPROPSTRTNMBR (which is the correct column name). The code is below. I cannot figure out why it's trying to pull this reference. Public Sub D2REDataCall() Call DBConnect sql = "SELECT CLMAILADDRESS,CLMAILCITY,CLMAILSTATE,CLMAILZIP,CLPROPSTRTNMBR,CLPROPSTREETADDR,CLPROPSTREETUNIT,CLPROPCITY,CLPROPSTATE,CLPROPZIP,CLPROPCOUNTY,CLCREDITREPORTDT,CLRATEEFFECTIVEDT,CLCLOSEDT,CLTOTALLOANAMT,CLINTERESTRATE,CLTILAPR,CLTERM FROM " & tblC...

combination clustered column and stacked bar chart
I have data concerning the repair turn-around for three products over several months. There are two components to the turn-around time for any item, i.e., actual labor time and wait time. I want to graph this as a clustered column, each cluster representing the data for a given month and each bar in a cluster representing one of the products. Now for the tricky part: I want each of the bars to be stacked showing the relationship between the actual labor time and the wait time. John, I don't know if I'm fully understanding what you want. Possibly these two examples can hel...

Source data, in Chart Menu, is grey and not accessible.
I set up a pivot table and designed a chart, saved and closed out of the workbook. Upon returning to work on this project, when selecting the chart menu to work with my source data, the source data is grey and not accessible. What might be causing this and can I correct it? It's a pivot chart. As soon as it's created, it's linked permanently to the pivot table, and you cannot change the source data. you cannot add more data that's not in the pivot table to the chart, nor can you remove data that is in the pivot table from the chart. - Jon ------- Jon Peltier, Microsoft...

Exchange 2003 Cross-site mailbox move
Hello all, I am currently upgrading an Exchange 5.5 Organization to Exchange 2003. This includes consolidating two E55 sites (siteA and siteB). I have already patched all my E55 servers (in siteA and siteB) with the post SP4 DS/IS hotfix and my E2k3 servers (in siteA) are at SP1. I can successfully perform a Cross-site mailbox move from siteB to siteA, during which the mailbox is properly deleted from the E55 server in siteB. The user can now logon to his Outlook client (which points to the E2k3 server in siteA) and send mail to all users, however only other users on the E2k3 server can se...

Missing Data Options
I am trying to use Tools - Options - Chart. I want to use the "not Plotted" option. When I click on it my chart does not change. This happens after I've selected the chart and then trying to make these changes. What am I doing wrong? I want to create a chart that ignores missing data, and gives me a trend line based on the data I have. I've had my nose in my Excel "bible" but can't find the answer. I greatly appreciate any tips. Jim -- jimsmith888 ------------------------------------------------------------------------ jimsmith888's Profile: http...

Extracting data and returing to colums.
I am about half way there, but I need some more help. These are web inquires that parsed out in the same colum and repeat th same way all down the page. I want to be able to use the fields as guidelines to pull the dat adjacent to it and sent to the new colum. In a way it is transposin all the data, but I need to pull all the first names of these peopl and have them align in the first colum. Same for Last name, business etc. So if the information that I have come across like this in th spreadsheet, I just want the customer infomation to move to th appropriate colums like the third example...

how do I convert Mozilla thunderbird data to outlook 2010?
Converted from an old Outlook to Mozilla Thunderbird years ago. Like new Outlook and would like to switch back. Can't seem to see a conversion utility -- Mishka I suspect you export from thunderbird to a format Outlook can import, eg csv (assuming pop mail) "Mishka" <Mishka@discussions.microsoft.com> wrote in message news:509C3250-167B-4FFF-9E07-5E5A8C625AA8@microsoft.com... > Converted from an old Outlook to Mozilla Thunderbird years ago. Like new > Outlook and would like to switch back. Can't seem to see a conversion > utility > -- >...

Graphing daily data
I am trying to graph daily data for a month in Excel 2007 that groups like days (Saturdays, Sundays, etc) in the same series, while still showing individual values and dates. Any ideas on how to accomplish this? Use your worksheet to pull the relevant data together into a series. Do the data manipulation in your worksheet, then the chart plots the series which you have generated in the worksheet. -- David Biddulph "Fozzie" <Fozzie@discussions.microsoft.com> wrote in message news:EB004134-AD88-434C-9DAD-BFE4CB2FD058@microsoft.com... >I am trying to graph daily data f...