Clear Contents Multiple Non-Continuous Ranges

Nov 16, 2006

what is described in the post clear "contents of rows based on cell contents in column" clear contents of rows based on cell contents in column

What is different is that for rows that I wish to leave untouched, I have in column A the text "Active". If that text is not present (""), then I need to clear contents for only certain cells in each row because others have formulas. For example, if A32 does not say "Active" I want to clear contents of cells D:32, G:32, etc while leaving the others in row 32 in tact. I need this to loop through about 400 rows. If it would make the macro easier, I can have the data in column "A" say "Inactive"/"Active" instead of blank/"Active"

View 4 Replies


ADVERTISEMENT

Clear Contents Of 2 Ranges On Multiple Sheets

Mar 29, 2012

I need to clear the the text in the same cell ranges on multiple worksheets. on a regular basis.

F7:K13
Q7:Q13

Is there a simple way to do this?

View 7 Replies View Related

Sum Multiple Non Continuous Ranges By 1 Criteria

Jan 18, 2008

I am trying to write an array using =sumproduct. I cannot get the second array to work. Whenever I split up the arrays they work fine separately, but not together. This is what I am putting. Any help is greatly appreciated. I am not sure if I am even using the correct formula.

=SUMPRODUCT((B37:B51="Math")+(F37:F51)+(I37:I51="Math")+(M37:M51)),[(B18:B31="Math")+(F18:F31)+(I18:I31="Math")+(M18:M31)]

So basically, I am creating a transcipt. So in column B if the subject is Math, I want column F to be added and so on and soforth. See there are more arrays, because it is broken down to grades 9, 10th, 11th, 12th. And what is being added is their credit (.5 or 1) for the class for each year.

View 9 Replies View Related

Charting Multiple Dynamic Ranges As Single Continuous Series

Mar 10, 2014

I have a log where I'm recording daily readings of a pipette. Each day three readings are taken and I have each stored as a dynamic range so that I can monitor how in control my process is. However, when I graph it, I would like it to be one continuous line graph (Three readings from day one followed by three readings from day two, etc.). I'm having difficulty doing any sort of combination.

View 7 Replies View Related

Clear Contents In Multiple WorkSheet

Dec 14, 2013

i have attached a template spreadesheet that 20 people uses everyday. The template works fine but what i would like to do is write a code to clear all the content that manually entered beforeclose. For some reason code is not working properly because when i wrote code to clear the contents the buttons don't work . I also attached the spreadsheet.

View 4 Replies View Related

Clear Contents Multiple Sheets

Dec 12, 2006

I have the following code for about 20 sheets

Sheets("Multi CT 43").Select
Cells.ClearContents
Range("A1").Select
Sheets("Multi CT 61").Select
Cells.ClearContents
Range("A1").Select
Sheets("Multi CT 80").Select
Cells.ClearContents
Range("A1").Select

i'm sure there is a more simple way of doing this but I can't find a way that works.

View 9 Replies View Related

Printing Of Non Continuous Ranges Of Data

Aug 11, 2006

I am trying to print non continuous ranges of data in the same worksheet using the print area - by keeping the ctrl button pressed etc... it works fine but the outcome is that the text in the pages becomes very smaller when compared to when you print page by page and fit every page to one.

View 2 Replies View Related

Copy Same Range To X Non-Continuous Ranges

Oct 10, 2007

I want to select a range of cell relative to A2 then copy and paste that range 12 times.

See my coding below. I have been getting a Warning box titled "Microsoft Visual Basic"
(X) 400.

Sub mycopytry()

Dim check As Integer

For check = 1 To 12
Range(ActiveCell.Offset(29, 0)).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
check = check + 1
Next

End Sub

View 9 Replies View Related

VBA To Create Charts/Graphs From Non Continuous Ranges

Aug 12, 2008

I have an excel sheet populated with loads of values. There is a space between each set of data. I need to draw a graph for each section and i cant work out how to do it. I have attached the sheet in question. The gap between each column has "space" written on row, which is needed to prevent it being deleted. The drawing of the graphs needs to be automated as there is going to be 100's of sheets containg lots of data. The column size of each section can change so it needs to be dynamic. I have attached an example sheet

View 5 Replies View Related

Non-Continuous Range In Event Code: Show A UserForm When A Cell In 1 Of 31 Named Ranges Is Selected

Nov 7, 2006

I am using the following Selection_Change Event to show a UserForm when a cell in 1 of 31 named ranges is selected.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Dim i As Long

For i = 1 To 31
If Not Intersect(Target, Range("StatPost" & i)) Is Nothing And Target.Value = "" Then
If Target.Offset(0, -8).Value = "" Or Target.Offset(0, -7).Value = "" Or Target.Offset(0, -6).Value = "" Or Target.Offset(0, -5).Value = "" Or Target.Offset(0, -3).Value = "" Or Target.Offset(0, -2).Value = "" Or Target.Offset(0, -1).Value = "" Then....................

View 3 Replies View Related

VBA Clear Contents

Feb 5, 2010

i am trying to use clearcontents with the below code but i get an error,


With Sheets(1)
With .Range("J7")
.ClearContents Range("J7:J" & .Range("A" & .Rows.Count).End(xlUp).Row)
.Formula = "=H7-I7"
.AutoFill Range("J7:J" & .Range("A" & .Rows.Count).End(xlUp).Row)
End With
End With

View 9 Replies View Related

Clear Contents Of Cells

Sep 10, 2013

I want the function of clear contents to work based on the value of B4. If I run the code with clear content part excel crushes.

VB:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B4") = "Basic" Then ''*** B4 is adropdown ***''
ActiveSheet.Unprotect
'Range("B10,F10,H10").ClearContents ''*** if I run this part the excel crushes ***'''
Range("B10,F10,H10").Locked = True

[Code] .....

View 4 Replies View Related

Clear Contents If 2 Characters Or Less

Feb 10, 2010

I am looking for a way to remove text from a cell if it is 2 characters or less but do nothing if the text is longer than that. Is there a way to do this with either a function or an advanced find?

For more detail, I have a list of middle initials and last names. The cells are either a middle initial by itself like "D." or they are a last name like Jones. I want to go through and delete the cells that have the middle initial and leave the ones with the last name. So if there is a function that will evaluate the cell, and if it is 2 characters or less, then delete the contents. If greater than 3, leave it alone.

View 3 Replies View Related

VB Code To Clear Contents

Jul 3, 2014

I have a range of cell content to be clear (Value only) but no formula in those cell.

The range is populated in cell H106.
Example cell
if cell H106 = D5:E102 then clear the value value only in cell range D5:E102
if cell C106 = G5:H102 then clear the value value only in cell range G5:H102.

View 8 Replies View Related

VBA To Clear Contents At Specified Intervals

Mar 6, 2014

I think I've had enough of using excel generated macro's as an excuse for decent code. I need to turn this:

Small procedure, function, what have you that will continue this pattern until I hit some kind of indicator to tell it to stop. This just clears the contents from 7 rows, hops down 9 rows and does it again.

View 14 Replies View Related

Clear Contents In Various Worksheets At Once?

Dec 2, 2011

I have a monthy activity where I would like to clear the area B13:P42 on various worksheets in the same spreadsheet. Is there an easier way to do this rather than just recording a macro?

View 5 Replies View Related

How To Clear Cell Contents

Jan 17, 2012

I have the following code where I want to clear the cells.

Code:

Workbooks(strOutputFileName).Sheets(2).Activate
Range("F9:F25,C27:E57,F59:F60,J9:J25,G27:I57,J59:J60,N9:N25,K27:M57,N59:N60,R9:R25,O27:Q57,R59:R60").Select
Selection.ClearContents

When I run this code, where it should select G27:I57, it instead selects G27:N57!!!!

When I manually try to select the G27:I57, the selection jumps to the right to N57.

When I use this piece of code on a different worksheet, it works OK!

View 6 Replies View Related

Clear Contents Of Entire Row?

Jan 21, 2012

Macro to clear contents of entire row if any cell in Range A2:A17 is Empty.

View 9 Replies View Related

Clear Contents Of Dropdown Box?

Feb 27, 2012

I am trying to have a macro clear out the contents of a cell before it quits, the cell contains validation data in the form of a list. My goal is to force the user to choose an option every time before exporting a file, the cell contains what will become a part of the filename. I want to make sure the user, usually me, remembers to change it each time rather than accidentally saving over another filename. I had the following code and kept getting a 400 error (nice and specific)

Code:
ActiveSheet.Range("O3").ClearContents

The only way my macro will run without a 400 error is if that line is commented out. Is there any way to clear out the cell and retain the validation data dropdown list?

View 1 Replies View Related

VBA To Clear Contents But Not Header?

Jul 30, 2014

i have a vba that clears content from specific cells, hence somehow it also deletes the header as well

This is my code:

Dim LastRow As Long
Sheets("All").Select
LastRow = Range("J" & Rows.Count).End(xlUp).Row
Range("A2:J" & LastRow).ClearContents
Sheets("All").Select
LastRow = Range("AO" & Rows.Count).End(xlUp).Row
Range("AI2:AO" & LastRow).ClearContents

View 9 Replies View Related

Macro To Clear Contents.

Aug 20, 2008

I'm trying to figure out code to have only the contents of cell 'a2' cleared when the contents of cell 'a1' is changed. The issue is that both cells are derived from lists and I don't want the formats cleared.

View 9 Replies View Related

Vba Clear Contents Of Column

Apr 8, 2009

I dont know how to copy an excel sheet into the question.

But assuming the following text starts at CELL C2 (this is just a section of the workbook)

C D E F G

1x55Correct!
2x55Try Again?
3x55Try Again?
4x55Try Again?
5x55Try Again?

This is a worksheet to test multiplication tables. I am trying to create a macro button to clear the contents of column F, for the kids to start again?

View 9 Replies View Related

Macro For Min Value Then Clear Contents

Sep 30, 2009

I need code for a macro that will find a min value in a particular column and based on that min value clear the contents of other cells for that min value.

For example, I have dates in column E such as:

200907
200906
200902
200809
200803
200710
200707

Therefore, I need to find all records for 200707 and clear the contents in other cells.

I can't hardcode the min date because that can change from month to month when new files are created. As can the number of rows.

I'm using Excel 2007.

View 9 Replies View Related

Clear Contents Macro

Jul 18, 2006

I have a protected worksheet in which certain calculations are performed based on certain inputs.

For example, let's take Column E:

Starting in cell E4, I have cells in that column which are input cells and further down the column are calculated cells which are locked and whose formulas are hidden.

I was looking for a macro which would go down col E and clear contents of all unlocked cells without messing up any of the protected cells.

Also, there are certain cells in which I want to clear contents and some cells that I want the macro to insert a "0" value.

The input cells are non-contigous.

View 7 Replies View Related

Clear A Cell's Contents

Mar 7, 2007

Is it possible using vba code to clear a specific cell's contents? I have a workbook that has text boxes that need the linked cell cleared when the workbook opens so the user can start fresh with empty text box's.

View 6 Replies View Related

Clear Contents Vs Delete

Apr 24, 2007

I've been pressing on with my project and figured out that I really do not know the difference between Deleting and Clear contents. I really thought they were the same but am now unsure. I guess I have 2 questions. Which is better to use? what I'm doing is removing old data and importing new data. and which is more efficient for writing code.

Sheets("Bodies"). Range("A2:Z65000").ClearContents

Sheets("bodies").Range("a2:Z65000").Delete

View 6 Replies View Related

Clear Contents Of Dotted Selection

Apr 24, 2014

I'm playing along with Excel. I discovered it doesn't like cutting and pasting in VBA. This screws up formulas in cells.

It much prefers copying and pasting. So this involves an extra step, going back to the cells you just copied and clearing them.

So I have a selection of cells and I just copied them to a different location. Naturally the location I just copied it to then becomes selected.

I would like to go back to the cells that have the dotted selection around them and clear them. If I simply use Selection.ClearContents it will clear the currently selected cells, NOT the dotted selection.

Command to clear the contents of the dotted selection?

View 4 Replies View Related

Clear Contents Of Last Column With Data

Jan 28, 2014

I am trying to change this code to clear the contents of the last column of my "Master sheet". I am having trouble indexing to the last column, however. I found this code online but it looks much more complicated than it needs to be. Is there any way to do this easier/change this code to fit my needs?

Right now, the letter of the column I want to delete pops up in a message box, but I'm not sure where to go from there.

[Code] .....

View 2 Replies View Related

Clear Contents Based On Criteria

Aug 28, 2008

I need some code that will clear the contents of a Row Range(C:T) if either of the contents of cells X and Y = "E"

The code needs to look at cells X17 and Y17 and continue looking until it gets to X216 and Y216

So - If X = E but Y does not, then the row range C:T must be cleared.
If Y = E but X does not, then the row range C:T must be cleared
If both X and Y = E, then the row range C:T must be cleared

If the letter E does not exist in either X or Y, then the row must be left alone

View 13 Replies View Related

Conditional Clear Contents Of Cells

Oct 10, 2008

I have a dropdown list in the range E17:E91 containing a list, and two of the values in the list are "DI" and "LTC". What I need to happen is if either of these are selected, the corresponding cell in column F needs to have it's contents cleared. Ex. if in E17 the value is ever either DI or LTC, cell F17 gets contents cleared. if column E contains ANYTHING ELSE, nothing happens. That's all, I've taken care of conditional formatting and data validation already. So even if data is pasted in the worksheet into column E it needs to recognize that and always act in real time.

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved