Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Macro-Issues with 0

Posted on 2011-03-10
10
Medium Priority
?
397 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
LVL 81

Accepted Solution

by:
byundt earned 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Utilizing an array to gracefully append to a list of EmailAddresses
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

636 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