Solved

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

Posted on 2014-10-30
20
105 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
[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
  • 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
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 47

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
 

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

Independent Software Vendors: 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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

749 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