Solved

How can I use a script to modify an Excel spreadsheet?

Posted on 2014-10-30
20
100 Views
Last Modified: 2014-11-26
I have an Excel spreadsheet which I would like to modify.

Actions I want to have done to the spreadsheet using a script.

If data exists in Column A1 and if the quantity shown in Column S1 is 0, then delete the whole line.  If S1 has nothing in it, then do not delete the line, only if it has a 0 in that field.
This has to continue for all columns it finds data for in the A column and down for the entire spreadsheet.

Then, after it does this, I would like to automatically delete the following columns:  B-R, and T-Z.
0
Comment
Question by:100questions
  • 6
  • 5
  • 5
  • +2
20 Comments
 
LVL 43

Expert Comment

by:Steve Knight
ID: 40414028
Is that within the spreadsheet.  I.e. A macro in there using vbs,  or external script in which case vbs is not too difficult.

Is this a proper spreadsheet not csv file   and what format xlsx xls  etc.

Suggest you post an example file.

Steve
0
 

Author Comment

by:100questions
ID: 40414203
Format is xls
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 40414293
See questions asked.
0
 
LVL 15

Expert Comment

by:ZabagaR
ID: 40414299
I'm interested to see what the solution is....I know almost nothing about the question but I googled Excel VBA and came to this link http://www.excel-easy.com/vba.html
I wrote a button/macro that did just about everything asked above in about 20 minutes.
So you could probably dig this up on your own without a lot of difficulty.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40414312
This code will remove rows with your specified criteria (i.e., a value in column A and 0 value (not empty) in column S), then delete columns B:R and T:Z
Option Explicit
Sub Clean_Up()
    Dim rng, rng2 As Range
    Dim cl As Object
    
    Set rng = Range("A1:A" & Cells.SpecialCells(xlLastCell).Row)
    
    For Each cl In rng
        If cl.Value <> "" And (cl.Offset(0, 18).Value = 0) Then
            If rng2 Is Nothing Then
                Set rng2 = cl
            Else
                Set rng2 = Union(rng2, cl)
            End If
        End If
    Next cl
    rng2.EntireRow.Delete
    
    Columns("T:Z").EntireColumn.Delete
    Columns("B:R").EntireColumn.Delete
    
End Sub

Open in new window


An example workbook is attached with test data.  The yellow rows should be deleted, leaving the rest.

Regards,
-Glenn
EE-Q28547896.xlsm
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 40414373
Well I was thinking similar lines to above, but after previous q writing solution in language asked for by this asker but ignored in preference to another language decided not to bother without any feedback first.

100 questions - why is it so difficult to look at the questions asked by someone trying to help you and just answer them!!

Steve
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40463497
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
 

Author Comment

by:100questions
ID: 40463718
@SteveKnight - Format is .xls, or .xlsx.  External script would be best.  I do not have a sample to post at this time.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40463774
@100questions:  So, my VBA solution will not work for you?
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:100questions
ID: 40464246
Glenn, was that an external script? Or a VB macro inside the spreadsheet?
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40464503
VBA macro inside the workbook, yes.  Did you not see my example file?  

You could run this macro on any other open workbook; it will process the active, visible sheet.
0
 

Author Comment

by:100questions
ID: 40464693
@GlennRay - thanks.  The spreadsheet with teh macro does not work exact as it should.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40464714
I'm sorry to hear that.  I'm sure I can resolve that if you would be more-specific as to what it is not doing.  My example workbook removes data in accordance with the description you provided in your original question.  If your actual data is different, that might be an issue.
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 40465200
or ... post an example worksheet as in post #1 then people have something to work with for your free consultancy where you seem to want complete solutions with no effort on your part constantly?
0
 

Author Comment

by:100questions
ID: 40465274
@GlennRay -  I used the exact spreadsheet you sent me, thank you.  I am expecting it to keep 7 lines, including the skipped lines.   The data looks like this once the macro is run:

Column A      Column S
keep      1
      
      
      1
      
keep      1


Unless one of them is hidden?
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 40465488
SO POST AN EXAMPLE SPREADSHEET!!!!!!
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40466005
Okay, I understand what the issue is and here is the corrected code:
Option Explicit
Sub Clean_Up()
    Dim rng, rng2 As Range
    Dim cl As Object
    
    Set rng = Range("A1:A" & Cells.SpecialCells(xlLastCell).Row)
    
    For Each cl In rng
        If cl.Value <> "" And cl.Offset(0, 18).Formula = "0" Then
            If rng2 Is Nothing Then
                Set rng2 = cl
            Else
                Set rng2 = Union(rng2, cl)
            End If
        End If
    Next cl
    rng2.EntireRow.Delete
    
    Columns("T:Z").EntireColumn.Delete
    Columns("B:R").EntireColumn.Delete
    
End Sub

Open in new window


Updated workbook attached.
-Glenn
EE-Q28547896.xlsm
0
 

Author Closing Comment

by:100questions
ID: 40466725
Thanks very much for all your assistance, works well.
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

This article was inspired by a question here at Experts Exchange (http://www.experts-exchange.com/Software/Photos_Graphics/Images_and_Photos/Q_28629170.html). The requirements stated in that question are (1) reduce the file size of a large number of…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

895 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

13 Experts available now in Live!

Get 1:1 Help Now