Results 1 to 3 of 3

Thread: Macro to clear contents

  1. #1
    Board Regular
    Join Date
    Jul 2003
    Posts
    252

    Default Macro to clear contents

    I have a macro that copies data (integers) produced by formulas and inputs them into my Range of AE8:AE31. The quantity of numbers can vary in each column.

    How can I create a macro to go to the first blank cell in my range and clear the contents.

    Example Data:
    AE8=1
    AE9=6
    AE10=7

    AF8=1
    AF9=2
    AF10=6
    AF11=8

    AG8=1
    AG9=2
    AG10=4
    AG11=5
    AG12=6


    In the above example:-
    Go to AE11 and clear contents from AE11:AE31
    Go to AF12 and clear contents from AF12:AF31
    Go to AG13 and clear contents from AG13:AG31

    Thankyou

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,933

    Default Re: Macro to clear contents

    Hi,

    I'm not totally clear on what you want. How do you determine where the end of the data is in each column? Does your data look like this?

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book1___Running: xl2002 XP : OS = Windows XP
    File Edit View Insert Options Tools Data Window Help About
    =

    AE
    AF
    AG
    AH
    8
    111
    9
    622
    10
    764
    11
    85
    12
    data 6
    13
    datadata
    14
    datadatadata
    15
    datadatadata
    16
    datadatadata
    17
    datadatadata
    18
    datadatadata
    19
    datadatadata
    20
    datadatadata
    21
    datadatadata
    22
    datadatadata
    23
    datadatadata
    24
    datadatadata
    25
    datadatadata
    26
    datadatadata
    27
    datadatadata
    28
    datadatadata
    29
    datadatadata
    30
    datadatadata
    31
    datadatadata
    Sheet2

    [HtmlMaker light Ver1.10] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    If so, this macro will find the first blank cell in each column and delete everything from there down to row 31.

    Code:
    Sub ClearCells()
    
    Range("AE31:AE" & Range("AE8").End(xlDown).Row + 1).ClearContents
    Range("AF31:AE" & Range("AF8").End(xlDown).Row + 1).ClearContents
    Range("AG31:AE" & Range("AG8").End(xlDown).Row + 1).ClearContents
    
    
    End Sub
    If I've got it wrong then please post some sample data, preferably using the HTML Maker addin from here.

  3. #3
    Board Regular
    Join Date
    Jul 2003
    Posts
    252

    Default Re: Macro to clear contents

    Hi Dan,
    Thanks for your reply. I've downloaded the HTML add-in but I don't know how to use it yet.

    My problem is driving me crazy....I've been on it for days. The frustrating part is that if I break my code up and run in two parts separately, the whole thing works BUT only if I enter part A's output manually. If I let the code pick up the output from part A, the results are corrupted. I've assumed it's because I'm picking up data from blank cells, hence my post, but maybe I'm wrong.

    Perhaps if I go back to the start and give you all my details you may have a look at it and see where the code is breaking down.

    My Problem:
    Enter a column of numbers. Range is B8:B31.
    In columns C8:E31, select a mixture of those numbers using tick boxes.
    The numbers will be random initially but part A of the code puts them together in ascending order.
    Use code to work out all the permutations of those selected numbers and output them to Range G9:I100+. (generally won't be more than 100).
    Exclude combinations of the same number in the output range.
    No blanks i.e. 1 blank 4 or 1,1,4 are invalid.

    Solution:
    Below is the code in two sections.
    The first copies the ticked selections and sorts them in ascending order and outputs them to AE8:AG8 down. They are all integers by the way. Works fine.

    The next part of the code computes all possible combinations and outputs the result in range G9:I9 down.

    The part not working.
    Part 2 of the code BUT.....
    If I manually overwrite all the numbers generated by part A in the range AE8:AG8 down and then clear the contents of all the non visible cells (they appear blank) in these 3 rows down to row 31 and then run part B of the code, it runs perfectly and computes all permutations correctly.

    Must be something obvious in the code I am overlooking.

    Can you help me resolve this please Dan. I'm totally frustrated.

    B8:B31 contains integers.
    This is the formula in C8 copied down ....
    =IF(AND(AB8=TRUE,B8<>""),B8,"")
    This is the formula in D8 copied down ....
    =IF(AND(AC8=TRUE,B8<>""),B8,"")
    This is the formula in E8 copied down ....
    =IF(AND(AD8=TRUE,B8<>""),B8,"")


    Code Part A

    Sub CopySels4Sorting()

    Range("F8").Select 'Needed to exit from box mode

    Application.ScreenUpdating = False

    Range("C8:E31").Select
    Selection.Copy
    Range("AE8").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.CutCopyMode = False

    Range("AE8:AE31").Select
    Selection.Sort Key1:=Range("AE8"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    Range("AF8:AF31").Select
    Selection.Sort Key1:=Range("AF8"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    Range("AG8:AG31").Select
    Selection.Sort Key1:=Range("AG8"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    Range("F8").Select
    Application.ScreenUpdating = True

    End Sub



    Code Part B

    Sub Compute()

    Dim lPtr As Long
    Dim lPtr1 As Long
    Dim RangeA As Range
    Dim RangeB As Range
    Dim RangeC As Range
    Dim A As Range
    Dim B As Range
    Dim C As Range

    Application.ScreenUpdating = False

    Range("G9:I100").Select
    Selection.ClearContents


    lPtr = Range("AE65536").End(xlUp).Row 'Input Range this macro
    Set RangeA = Range("AE8", Cells(lPtr, 31))

    lPtr = Range("AF65536").End(xlUp).Row
    Set RangeB = Range("AF8", Cells(lPtr, 32))

    lPtr = Range("AG65536").End(xlUp).Row
    Set RangeC = Range("AG8", Cells(lPtr, 33))

    lPtr1 = 9 'Start at line G9
    For Each A In RangeA
    For Each B In RangeB
    For Each C In RangeC
    If (A.Value <> B.Value) _
    And (A.Value <> C.Value) _
    And (B.Value <> C.Value) Then
    Cells(lPtr1, 7).Value = A.Value '7=G
    Cells(lPtr1, 8).Value = B.Value '8=H
    Cells(lPtr1, 9).Value = C.Value '9=I
    lPtr1 = lPtr1 + 1
    End If
    Next C
    Next B
    Next A

    Range("F8").Select
    Application.ScreenUpdating = True

    End Sub

    Both part A and B of the code will eventually be combined to form one macro. Just broke it up here to demonstrate the problem.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com