Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2014-10-30
20
Medium Priority
?
114 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
18 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 50

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 2000 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

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

578 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