UtterAccess Discussion Forums > A little more formatting

UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel

davpoole

Aug 1 2006, 02:01 PM

I have found bits and pieces of what I need on this forum, but do to my level of knowledge...I need help putting them together.
I have a macro that does some simple formatting to the attatched sheet and what you see is the end result. I would like to add some code that would finish it up

In the Order id column i need to find the first empty row and delete all rows after that
In the Item Description column i need to move all the "X"'s to the Comment column
and finally
In the Qty Done column i need to move all the cells that contain data to the Item Description column.

Does that make sence to anyone? I appreciate any help I can get!

Luceze

Aug 1 2006, 03:14 PM

Hi Dave,

See if this will do what you want.

CODE

[color="blue"]Sub[/color] FormatIt()
Application.ScreenUpdating = [color="blue"]False[/color]
    [color="blue"]Dim[/color] rng1 [color="blue"]As[/color] Range, rng2 [color="blue"]As[/color] Range, rng3 [color="blue"]As[/color] Range
    [color="blue"]Dim[/color] cl [color="blue"]As[/color] Range
    Range(Range("A1").End(xlDown)(2), Range("A65536")).EntireRow.Delete
    ActiveSheet.UsedRange
    [color="blue"]Set[/color] rng1 = Range("F1", Range("F65536"))
    rng1.AutoFilter 1, "X"
    [color="blue"]Set[/color] rng2 = Range("F2", Range("F65536").End(xlUp)).SpecialCells(xlCellTypeVisible)

        [color="blue"]If[/color] Intersect(rng2, Range("F1")) [color="blue"]Is[/color] [color="blue"]Nothing[/color] [color="blue"]Then[/color]
            [color="blue"]With[/color] rng2
                .Offset(0, 2) = "X"
                .ClearContents
            [color="blue"]End[/color] [color="blue"]With[/color]
        [color="blue"]End[/color] [color="blue"]If[/color]

    rng1.AutoFilter
    [color="blue"]Set[/color] rng3 = Range("G1", Range("G65536").End(xlUp))
    rng3.AutoFilter 1, "<>"

    [color="blue"]If[/color] Intersect(rng3, Range("g1")) [color="blue"]Is[/color] [color="blue"]Nothing[/color] [color="blue"]Then[/color] [color="blue"]Exit[/color] [color="blue"]Sub[/color]

        [color="blue"]For[/color] [color="blue"]Each[/color] cl [color="blue"]In[/color] Range("G2", Range("G65536").End(xlUp)).SpecialCells(xlVisible)
            [color="blue"]If[/color] cl <> vbNullString [color="blue"]Then[/color]
                cl.Offset(0, -1) = cl
                cl.ClearContents
            [color="blue"]End[/color] [color="blue"]If[/color]
        [color="blue"]Next[/color]
    rng3.AutoFilter
        [color="blue"]Set[/color] rng1 = [color="blue"]Nothing[/color]
        [color="blue"]Set[/color] rng2 = [color="blue"]Nothing[/color]
        [color="blue"]Set[/color] rng3 = [color="blue"]Nothing[/color]
[color="blue"]End[/color] [color="blue"]Sub[/color]

davpoole

Aug 1 2006, 03:24 PM

That worked like a charm!! Thank you, not only for the fast responce but also for making my life here at work a little bit easier!!!!!!!!

Luceze

Aug 1 2006, 03:31 PM

Anytime.

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.