Macro to clear contents

RJC

Active Member
Joined
Jul 29, 2003
Messages
252
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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?
Book1
AEAFAGAH
8111
9622
10764
1185
12data6
13datadata
14datadatadata
15datadatadata
16datadatadata
17datadatadata
18datadatadata
19datadatadata
20datadatadata
21datadatadata
22datadatadata
23datadatadata
24datadatadata
25datadatadata
26datadatadata
27datadatadata
28datadatadata
29datadatadata
30datadatadata
31datadatadata
Sheet2


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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top