Solved

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

Posted on 2014-10-30
20
98 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 45

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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

If like me you are one who spends a lot of time working and scripting with cmd.exe, sometimes it is handy to be able to quickly view a calendar for a given month and year. This script will quickly do just that!  Save the code posted below to a .bat …
You may have already been in the need to update a whole folder stucture using a script. Robocopy does it well and even provides a list of non-updated files in a log (if asked to). Generally those files that were locked by a user or a process by the …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

705 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

18 Experts available now in Live!

Get 1:1 Help Now