none
Blank Cells Above... in Sort Command RRS feed

  • Question

  • I have a little problem with this job... I have created input screen with 12 Items : e.g

    ITEM ONE: Motor Vehicle

    Voucher No.      Particular     Amount

    014656             Repairs       76,773

    Each of the items has headings similar to the one above but with different titles (Sheet1).

    In Sheet2 I have created a central page to reference links btw each of the particulars and Voucher no of the 12 item.   The Voucher Nos. are supposed to be organize in ascerding order on sheet2 but when I sort them several blank cells will appear above moving all my detail far below the range  I created; now I since I av created 20 spaces on sheet1 for entry under each of the Item.  And 20 rows each in Sheet2 also to link to the various Items in sheet1.   Also one sheet I created a Macro button to automate Sort and Save As.   But the result is not favorable.  -- please help me to modify it to exclude the blank cells above and make the sorted (Ascending) range start from top cells....

    Here is my code bellow:

    Sub RoundDiagonalCornerRectangle2_Click()
    '
    ' RoundDiagonalCornerRectangle2_Click Macro
    '
    Dim OptionA As Variant
    '
        Range("A9:Q236").Select
        ActiveWindow.LargeScroll ToRight:=-1
        ActiveWorkbook.Worksheets("Summary").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Summary").Sort.SortFields.Add Key:=Range("A9"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Summary").Sort
            .SetRange Range("A9:Q236")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        ActiveWindow.SmallScroll Down:=-219

        Range("A1").Select

        OptionA = InputBox("Type Filename Here", "Save As Option")

        'Worksheets("Summary").SaveAs (OptionA)

        If OptionA = "" Then

        MsgBox ("Name Your File")

        'Application.Quit

        Else

       Worksheets("Summary").SaveAs (OptionA)

        End If

        'OptionA = ""

    End Sub

    Abraham Eninla

    (Thanks in anticipation of your prompt response).

    Saturday, June 30, 2012 9:43 PM

All replies

  • give us some snapshot of the sheet.

    Try to get the range dynamically.By End method which will return last used row in A column

         .SetRange Range("A9:Q236")

    Change to

         .SetRange range(range("q9"),range("a9").end(xlDown)).address

    ---------------------------------------------------------------------------------------------

    Please do not forget to click “Vote as Helpful” if any post helps you and mark as Answer if it solves the issue.

    Monday, July 2, 2012 6:56 AM
    Answerer