Results 1 to 3 of 3

Macro to clear contents

This is a discussion on Macro to clear contents within the Excel Questions forums, part of the Question Forums category; I have a macro that copies data (integers) produced by formulas and inputs them into my Range of AE8:AE31. The ...

  1. #1
    RJC
    RJC is offline
    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
    dk
    dk is offline
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,934

    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
    RJC
    RJC is offline
    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