# 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. ## 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. ## 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
 AE8 =

AE
AF
AG
AH
8
111*
9
622*
10
764*
11
*85*
12
data*6*
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
 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. ## 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 ....

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.

#### Posting Permissions

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