Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts

    Modifying a macro

    I have two Excel macros that I often run consecutively. I need to filter the results in the second one.

    The first does a sort over a named range:

    Code:
    Application.Goto Reference:="Sort_Block_1"
        Selection.Sort Key1:=Range("B11"), Order1:=xlDescending, Key2:=Range( _
            "E11"), Order2:=xlAscending, header:=xlGuess, OrderCustom:=1, MatchCase _
            :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
            DataOption2:=xlSortNormal
    The second one outputs a range from the spreadsheet that contains the range I just sorted:

    Code:
    With ActiveWorkbook.PublishObjects.Add( _
            SourceType:=xlSourceRange, _
            Filename:=strMyDocs & "\page1.htm", _
            Source:="grades_1", _
            HtmlType:=xlHtmlStatic)
            .Publish (False)
            .AutoRepublish = False
       End With
    I'm not sure if it matters, but the ranges in the first and second macros intersect, but the one is not a subset of the other.

    Here's what I need to do. The column B contains a 0/1 index variable. I'd like to have the second macro go through each row, and not show the entries in certain columns in the outputted HMTL file if the indicator in column B is zero.

    I attached a simple spreadsheet as an example.
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Boo,

    Not having access to your actual sheet I can only take a stab at it but what I would suggest is to copy the Range to be outputted to HTML to another sheet then run a macro which will loop through the data and blank cols C & E where B = 0 then write it to HTML. Once you write it out the macro could then delete the created sheet.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts
    I only included a stub spreadsheet because the real one is large. I figure the solution I need doesn't need all that detail.

    I am not wedded to this suggestion, but I think this is what I would do in other programs that I understand better. Currently this is beyond me in Excel. I do think I'd be more comfortable if the new sheet copied everything as a value though.

    Anyone have a better suggestion?

  4. #4
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Always mention your excel version. You apparently attached an .xlsX file withOUT your macros and using xl2003 code. Try again with an .xlsm file and what you already have tested.

  5. #5
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts
    I've attached another file. It's an xltm from Excel 2013.

  6. #6
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Don't see att. Maybe save as .xlsM or .xlsB instead and attach.

  7. #7
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts
    Try this. It's an xlsm based on the xlmt.
    Attached Files Attached Files

  8. #8
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Easy to simplify with a loop macro. Will work on in tomorrow am.
    for i = 1 to 6
    do things to each i
    next i

  9. #9
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Working on this but don't understand sorting thru col e but publishing thru col H
    Please clarify.
    And, what about the print macros in module 5

  10. #10
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts
    Reply to Maudibe: This isn't quite right. I don't want to permanently delete the information in those cells. All I want to do is export an HTML file in which those values are blanked.

    Reply to dguillett@gmail.com: 1) I'm sorting an entire named range by column B then column E — so I'm not sure what your issue is, 2) then I only output to HTML portions of that named range, and 3) you don't need to worry about the print macro (it's for my recordkeeping, not public viewing), 4) the number of rows in each sheet won't be constant so doing an "until" there's a blank cell will work better than an "if" over a fixed number of cells.

  11. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    I need is to take the worksheets after I've run the "sort" macro, and replace columns E through L with blank cells for any row in which the entry in column B is a zero.
    One approach might be to save the values to an array variable before clearing them, output sheet to HTML, then place the values back.
    Last edited by Maudibe; 2014-09-05 at 17:58.

  12. #12
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts
    Fair enough. But I don't know how to do that. Thus the thread.

  13. #13
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Forget the first question. I have that "sorted" out. The question now is do you want the htm one for each sheet or put them all together (that are not 0) and then publish with all on one.

  14. #14
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Thought you may want to see the sort part

    Sub SortPart()
    dim i as integer
    dim lrb as integer

    For i = 1 To Sheets.Count
    With Sheets(i)
    lrb = .Cells(11, 2).End(xlDown).Row
    'Sort em
    .Rows("11:" & lrb) _
    .Sort Key1:=.Cells(11, 2), Order1:=xlDescending, _
    Key2:=.Cells(11, "e"), Order2:=xlAscending, _
    Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    '=============
    lrb = Application.Match(0, .Columns(2), 0) - 1
    If lrb > 10 Then
    MsgBox lrb
    End If

    End With
    Next i

    End Sub

  15. #15
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts
    1) The current exportHTML macro exports 2 named ranges from each sheet as individual HTML files, so 6 worksheets in my workbook yields 12 files on my desktop.

    2) Your SortPart macro runs, but I'm not really sure what the point of it is; I already have a macro that does the sorting. And what is the point of the MsgBox? Is this kind of a "proof of life" that you're working on this and part way done?

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •