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

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.
100questionsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve KnightIT ConsultancyCommented:
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
100questionsAuthor Commented:
Format is xls
0
Steve KnightIT ConsultancyCommented:
See questions asked.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

ZabagaRCommented:
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
Glenn RayExcel VBA DeveloperCommented:
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
Steve KnightIT ConsultancyCommented:
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
Martin LissOlder than dirtCommented:
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
100questionsAuthor Commented:
@SteveKnight - Format is .xls, or .xlsx.  External script would be best.  I do not have a sample to post at this time.
0
Glenn RayExcel VBA DeveloperCommented:
@100questions:  So, my VBA solution will not work for you?
0
100questionsAuthor Commented:
Glenn, was that an external script? Or a VB macro inside the spreadsheet?
0
Glenn RayExcel VBA DeveloperCommented:
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
100questionsAuthor Commented:
@GlennRay - thanks.  The spreadsheet with teh macro does not work exact as it should.
0
Glenn RayExcel VBA DeveloperCommented:
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
Steve KnightIT ConsultancyCommented:
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
100questionsAuthor Commented:
@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
Steve KnightIT ConsultancyCommented:
SO POST AN EXAMPLE SPREADSHEET!!!!!!
0
Glenn RayExcel VBA DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
100questionsAuthor Commented:
Thanks very much for all your assistance, works well.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.