Solved

Macro-Issues with 0

Posted on 2011-03-10
10
391 Views
Last Modified: 2013-12-26
Need a little help with this Marco i have been creating. Basically i am taking a data file and formatting it in Excel then saving the formatted data as a new filename of the users choice forcing CSV. The issue i have is i am bulk applying the formula to a set number of row/columns to cover different amounts of incoming data. The OFFSET formatting works fine but if a OFFSET cell is linked to a cell with no data it displays a "0".

I need a way to remove the "0"s in the final saved file, or remove them before i create the new file. I can think of ways to remove the all "0"s but there are some i want to keep. See below for better understanding.

Bold "0"s are good, 0,0,0,0,0 are bad, how do i tell excel this!
A,B,C,D,234
A,B,C,D,33
A,B,C,D,0
A,B,C,D,34
A,B,C,D,0
A,B,C,D,3.9
A,B,C,D,3
A,B,C,D,3
0,0,0,0,0
0,0,0,0,0
0,0,0,0,0
0,0,0,0,0
0,0,0,0,0
0,0,0,0,0


Sub Pastemacro()
'
' Pastemacro Macro
' Created by Daniel Pelletier 03/09/11

' Set up Forumla in cells 5 by 300 Starting in B
Range("B1").Select
ActiveCell.FormulaR1C1 = _
        "=OFFSET(R1C1, (ROW()-1)*5+INT((COLUMN()-2)),MOD(COLUMN()-2,1))"
    Selection.AutoFill Destination:=Range("B1:F1"), Type:=xlFillDefault
    Range("B1:F1").Select
   Selection.AutoFill Destination:=Range("B1:F300"), Type:=xlFillDefault
    Range("B1:F300").Select
    
   'Open Data File, Copy Data
    ChDir "C:\Program Files\Worth Data\TriCoder Utilities"
    Workbooks.OpenText Filename:= _
        "C:\Program Files\Worth Data\TriCoder Utilities\DATA FILE #0.dat", Origin:= _
        437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False _
        , Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True
    Columns("A:A").Select
    Selection.Copy
    
    'Paste Data into Main WB
    
    Windows("Upload_Scanner_Data.xls").Activate
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'Create New workbook, Copy and paste formatted data in new WB
    
    Range("B1:F300").Select
    Application.CutCopyMode = False
    Selection.Copy
    Workbooks.Add
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False
    Application.CutCopyMode = False

'Ask user for file name and location, auto select CSV
   
   Dim file_name As Variant
file_name = Application.GetSaveAsFilename(FileFilter:="CSV (Comma delimited) (*.Csv), *.csv")
    If file_name <> False Then
      ActiveWorkbook.SaveAs Filename:=file_name, FileFormat:= _
        xlCSV, CreateBackup:=False
      MsgBox "File Saved!"
    End If
  

    
End Sub

Open in new window

0
Comment
Question by:Daniel_P67
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 24

Expert Comment

by:StephenJR
ID: 35097170
Could you post a sample workbook?
0
 
LVL 31

Expert Comment

by:farzanj
ID: 35097244
You could use SED to remove those

sed "s/,0$//" filename

Open in new window

0
 
LVL 1

Author Comment

by:Daniel_P67
ID: 35097360
Looking up sed, not sure how it works.

Attached sample workbook, datafile, data file was renamed to .txt so i could upload it. Change back to .dat
Upload-Scanner-Data-Share.xls
DATA-FILE--0.txt
0
 
LVL 31

Expert Comment

by:farzanj
ID: 35097731
What is exactly that you want to do.

If the file is produced on the Unix/Linux side and you need to remove last ",0" , wherever it exists, that is one simple sed command.  If you would like to write an excel macro to delete one last column, that is possible too.  Please let me know.
0
 
LVL 1

Author Comment

by:Daniel_P67
ID: 35097781
The data file comes from a Hand held device i can not run any commands on it. I need to take that file, reformat the data and convert it to CSV with different filenames .


Raw file

a
b
c
d
3
a
b
c
d
3
a
b
c
d
3
a
b
c
d
3
a
b
c
d
3
a
b
c
d
3

Converted to csv in the following format
a,b,c,d,3
a,b,c,d,3
a,b,c,d,3
a,b,c,d,3
a,b,c,d,3

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 35097925
I added statements to get rid of the rows with zeros. I also turned off screen updating so the macro runs much faster. And I got rid of the Select statements, once again so macro runs faster.

You will note that I put the .dat file in a different location than you did. You will need to change it back to your location. You may also need to adjust the file names (the ones you posted were named slightly differently than your macro expected).

Sub Pastemacro()
Dim rg As Range
Dim i As Long
' Pastemacro Macro
' Created by Daniel Pelletier 03/09/11

' Set up Formula in cells 5 by 300 Starting in B
Application.ScreenUpdating = False
Range("B1").FormulaR1C1 = _
        "=OFFSET(R1C1, (ROW()-1)*5+INT((COLUMN()-2)),MOD(COLUMN()-2,1))"
    Range("B1").AutoFill Destination:=Range("B1:F1"), Type:=xlFillDefault
    Range("B1:F1").AutoFill Destination:=Range("B1:F300"), Type:=xlFillDefault
    Range("B1:F300").Select
    
   'Open Data File, Copy Data
    'ChDir "C:\"
    ChDir "C:\"      'Please change Brad's test path in next statement
    Workbooks.OpenText Filename:= _
        "C:\VBA\Sample '11\DATA-FILE--0.dat", Origin:= _
        437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False _
        , Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True
    Columns("A:A").Copy
    
    'Paste Data into Main WB
    
    ThisWorkbook.Activate
    Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'Create New workbook, Copy and paste formatted data in new WB
    
    Application.CutCopyMode = False
    Range("B1:F300").Copy
    Workbooks.Add
    Range("B1:F300").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False
    Application.CutCopyMode = False
    
    Set rg = Range("B1:F300")
    For i = rg.Rows.Count To 1 Step -1
        If Application.CountIf(rg.Rows(i), 0) = Application.CountA(rg.Rows(i)) Then rg.Rows(i).EntireRow.Delete
    Next
Application.ScreenUpdating = True

'Ask user for file name and location, auto select CSV
   
   Dim file_name As Variant
file_name = Application.GetSaveAsFilename(FileFilter:="CSV (Comma delimited) (*.Csv), *.csv")
    If file_name <> False Then
      ActiveWorkbook.SaveAs Filename:=file_name, FileFormat:= _
        xlCSV, CreateBackup:=False
      MsgBox "File Saved!"
    End If
    ActiveWorkbook.Close SaveChanges:=False
    
End Sub

Open in new window


Brad
0
 
LVL 1

Author Comment

by:Daniel_P67
ID: 35098211
Could you explain the logic behind the quoted code? How does it know to what rows to delete?

Thanks! I change the file names when i uploaded the file, sorry for missing something. The Code works great, made a change to paste the data to A-E instead of B-F like how you re-wrote it. Not that i am complaining!


Thanks for those speed up tips!

Set rg = Range("A1:E300")
    For i = rg.Rows.Count To 1 Step -1
        If Application.CountIf(rg.Rows(i), 0) = Application.CountA(rg.Rows(i)) Then rg.Rows(i).EntireRow.Delete
    Next

Open in new window

0
 
LVL 81

Expert Comment

by:byundt
ID: 35099042
Daniel_P67,
The code loops through the rows from the bottom up. If you go from the top down, then you lose your place (need to tweak both the row counter and loop ending test) after deleting a row.
rg.Rows.Count           'Returns the number of rows in the range rg. It's 300 in your situation, but calculating it makes the code easier to update.

Open in new window

The macro then uses the worksheet functions COUNTA and COUNTIF. COUNTA returns a count of all the cells with numbers or text. COUNTIF returns a count of all the cells containing 0. If the two counts agree, then the row contains only 0 or blanks--and should be deleted.

Brad
0
 
LVL 1

Author Comment

by:Daniel_P67
ID: 35099676
Thanks! I like to understand what i am writing.
0
 
LVL 1

Author Closing Comment

by:Daniel_P67
ID: 35099694
Great, just what i was looking for.


Thanks!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now