complex selection using rc range format

offseyeds

New Member
Joined
Nov 17, 2004
Messages
25
In my sheet i have specific cells that need to be selected. I have other vba formulas that copy specific things into these cells based off other information. I have it set up to do these operations based of the cell that is selected. So in my vba i have my code based off the rc format not just a specific cell. This is because i want to use the same macro and on different colums but want it applied to the same rows in different colums. The only problem is that i want to be able to just select the specific cells for formating such as background color.

Heres The Jist of what I am doing!

When using the rc format i place my cursor on cell D13 and then use the following vba coding, Range(Activecell(1,1), ActiveCell(8,1)).Select, creates a selection of D13:D20.Then if i wanted to clear that selection i would enter Selection.ClearContents. Then to get to the next selection my formula would be Range(Activecell(12,1), ActiveCell(19,1)).Select and this would give me a selection of D24:D31. Then i could clear those contents using the same formula Selection.ClearContents. Now i understand by using specific cells this would look like Range("D13:D20,D24:D31").Select . Then the following code would be Range("D24").Activate . That gives me two seperate ranges active at the same time. I can then clear the contents of both those selections at once or change the format of those cells. How would i do this using my activecell rc format. Is there a way to activate more than one selection. Thank you for any help, i have tried many things and none of them work.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
Could you explain further what you are actually trying to achieve?

And can you post your existing code?

Something like this might be of use:

Code:
Dim rng as Range

     Set rng = ActiveCell
     Set rng = rng.Resize(7,1)
     rng.ClearContents
     Set rng = rng.Offset(9,0)
     rng.ClearContents
 

offseyeds

New Member
Joined
Nov 17, 2004
Messages
25
Sorry about the confusion. Here is my macro that clears each selection in my spreadsheet.

Range(ActiveCell(1, 1), ActiveCell(8, 1)).Select
Selection.ClearContents
Range(ActiveCell(12, 1), ActiveCell(19, 1)).Select
Selection.ClearContents
Range(ActiveCell(12, 1), ActiveCell(19, 1)).Select
Selection.ClearContents
Range(ActiveCell(12, 1), ActiveCell(18, 1)).Select
Selection.ClearContents
Range(ActiveCell(11, 1), ActiveCell(26, 1)).Select
Selection.ClearContents
Range(ActiveCell(20, 1), ActiveCell(25, 1)).Select
Selection.ClearContents
Range(ActiveCell(10, 1), ActiveCell(22, 1)).Select
Selection.ClearContents
Range(ActiveCell(16, 1), ActiveCell(25, 1)).Select
Selection.ClearContents
Range(ActiveCell(14, 1), ActiveCell(20, 1)).Select
Selection.ClearContents
Range(ActiveCell(11, 1), ActiveCell(16, 1)).Select
Selection.ClearContents
Range(ActiveCell(10, 1), ActiveCell(17, 1)).Select
Selection.ClearContents
Range(ActiveCell(12, 1), ActiveCell(19, 1)).Select
Selection.ClearContents
Range(ActiveCell(12, 1), ActiveCell(19, 1)).Select
Selection.ClearContents
Range(ActiveCell(12, 1), ActiveCell(19, 1)).Select
Selection.ClearContents
Range(ActiveCell(12, 1), ActiveCell(18, 1)).Select
Selection.ClearContents
Range(ActiveCell(11, 1), ActiveCell(18, 1)).Select
Selection.ClearContents
Range(ActiveCell(12, 1), ActiveCell(24, 1)).Select
Selection.ClearContents
Range(ActiveCell(17, 1), ActiveCell(29, 1)).Select
Selection.ClearContents
Range(ActiveCell(17, 1), ActiveCell(34, 1)).Select
Selection.ClearContents
Range(ActiveCell(22, 1), ActiveCell(29, 1)).Select
Selection.ClearContents
Range(ActiveCell(12, 1), ActiveCell(19, 1)).Select
Selection.ClearContents
Range(ActiveCell(12, 1), ActiveCell(20, 1)).Select
Selection.ClearContents
Range(ActiveCell(13, 1), ActiveCell(32, 1)).Select
Selection.ClearContents
Range(ActiveCell(24, 1), ActiveCell(28, 1)).Select
Selection.ClearContents
Range(ActiveCell(9, 1), ActiveCell(16, 1)).Select
Selection.ClearContents
Range(ActiveCell(12, 1), ActiveCell(19, 1)).Select
Selection.ClearContents
Range(ActiveCell(12, 1), ActiveCell(18, 1)).Select
Selection.ClearContents
Range(ActiveCell(11, 1), ActiveCell(18, 1)).Select
Selection.ClearContents
Range(ActiveCell(12, 1), ActiveCell(30, 1)).Select
Selection.ClearContents
Range(ActiveCell(23, 1), ActiveCell(29, 1)).Select
Selection.ClearContents
ActiveCell.Offset(-366, 0).Select

It doenst matter what column you are on but if you put your curson on cell D13 then it clears each of those selections. You could be on G13 or X13 and i will travel down the colum and clear only the specified rows in that column. Now i have it this way because my spreedsheet works like this

In cell A13:A20 i have item names and in cells D13:D20 i import bid numbers for those items and in cells G13:G20 i import actual cost for those items and then they are totaled in cell D21 and G21 then i go to my next set of items in cells A24:A31. bid numbers go in D24:D31 and actuals in G24:G31 and are totaled in D32 and G32, and so on for items and numbers. The above formula if my cursor is on cell D13 or cell G13 will clear all my numbers all the way down. Is there a way to just select and keep all the ranges active without using the above format where you have to perform the action then go to the next selection. I want this macro to work no matter what column i have my cursor on. If its on G13 i want it to select all the same ranges that are above and keep them selected. Then i might want to go back to D13 and select all of those cells and change the background color. I hope this explains in more detail what i am wanting to do.
 

Forum statistics

Threads
1,147,622
Messages
5,742,205
Members
423,711
Latest member
luisfreitas

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
Top