Clear Column Data With Range

Jun 15, 2014

I have

colpaste1 =Range("D2:D2")=BA
colpaste3 =Range("D4:D4")=BC

If Cell D2 = BA and D4= BC, i need VB Code to clear all contents in column BA to BC.

Note: BA and BC are the columns.

View 2 Replies


ADVERTISEMENT

Clear Contents Of Column In Range Variable?

Feb 11, 2014

I have a range variable named data I want to clear all the data in the 3rd column in the range variable I know how to reference a single location in the range variable but not a whole column. see example code below

[Code] .....

View 3 Replies View Related

Clear Contents Of Column Based On Data In Another Column?

Apr 10, 2014

I need to clear the contents of a columns G and H starting 11th row based on what is there in F column. The macro should check for last non-empty cell in column F starting F11 (assume it finds F30 to be last non-empty cell), then it should clear the contents of G11 to G30, H11 to H30.

View 1 Replies View Related

Clear 1 Column Of Data

Oct 4, 2006

how do I specify in the code to clear Just column A?

View 3 Replies View Related

Clear, External Data Range..

Jan 11, 2007

I found a way to import some data from some csv files, and it works well for me. I don't want to keep the option to refresh the data. I can go into each tab created from each import and and uncheck the box Data>Import External Data>Data Range Properties>"save query definition" that removes it for me. Is there a way to do that with code. It doesn't work with the macro recorder.

Sub ImportOnline()
On Error Resume Next
Dim DayFile As String
DayFile = InputBox("Enter Date of File (MMDDYY)")
Sheets.Add
ActiveSheet.Select
ActiveSheet.Name = "B-" & DayFile
Application.ScreenUpdating = False
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:My DocumentsB-" & DayFile & ".txt", _
Destination:=Range("A1"))
.Name = DayFile
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False................

View 3 Replies View Related

Clear Old Data And Add New Data Every Month, The Named Range Changes

Dec 13, 2008

I have 30 worksheets in a workbook in which the first sheet is called summary. The summary sheet has 29 formulas with all named ranges, so one named ranged per worksheet. The issue I have is when i clear old data and add new data every month, the named range changes on some worksheets not pointing to the correct range as previous month file even if I have $

View 9 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 Used Range

Feb 21, 2010

I have a table on a worksheet and have for quite some time used a very successful macro UsedRangeClear. I found here on the forum. Anyway the macro still works on all tables on all other sheets except this one. This is the reason, somehow someone, maybe even me, copied something down or formated down to the 1,048,152th row of the worksheet. My workbook has grown from 5000 kb to 11,000 kb.

I have tried to highlight and deleted the rows, highlight and clear contents and the following macros:

Sub UsedRangeClear()
On Error Resume Next
ActiveSheet.Unprotect Password:="myfadra"
Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.UsedRange
ActiveSheet.Protect Password:="myfadra"
End Sub

Sub ReSetTable_UsedRange()
'For ICFMR
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).row

On Error Resume Next
ActiveSheet.Unprotect Password:="myfadra"
Application.ScreenUpdating = False
Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.ListObjects("Table7").Resize Range("A1:T" & LR)
ActiveSheet.Protect Password:="myfadra"
Application.ScreenUpdating = True
End Sub

Each option freezes Excel. I am sure there is something easy I am just missing. Soooo, Please offer any suggestions you can think of.

A Loyal Mr. Excel Follower
Alecia

View 9 Replies View Related

Count How Many Cells Have Data In One Column If Within Date Range In Another Column?

Jan 22, 2014

I am trying to Count (or counta?) cells in one column if they have data but only if within a date range in another column.

So, in the example below, I would want to count how many cells in column E have data in them, but only if the date in column A falls between Jan 1 2014 and Jan 5 2014. (In this case, result should be 4).

A
B
C
D
E
01/01/2014

[Code].....

View 4 Replies View Related

Clear Range With Variable?

May 8, 2014

I need a simple VB Code to clear the cell :

K106 = BI210:BU269

Need to clear the range BI210:BU269

if K106 = BI271:BU341 then need to clear range BI271:BU341

View 2 Replies View Related

Clear Autofilter Range

Nov 28, 2006

I'm wrinting a macro to copy specific data from a table. To do that I'm using a Autofilter and a list of criteria. The macros works fine for existent data, I'm mean, when the result of the filter is not null, but when the filter doesn't find a result, instead of clearing the filter range, it keeps the last valid. The result is a colapse and freeze the Excel. I'd like to know how can I reset or clear the filter range?
The code I'm using is:


Sub Filter_01()

'Definições preliminares
Dim rng As Range
Dim rng2 As Range
Dim ARLE
Dim filter_valid, filter_invalid As Variant

filter_valid = Array()
filter_invalid = Array()
'Criação do Arquivo de Destino
Caminho = "D:Documents and Settingscjcs.ABMeus documentosAutomaSIPPPlanilhas"
Nome_Arquivo = "Produtos_Tanques.xls"
Nome_Completo = Caminho & "" & Nome_Arquivo ...

View 9 Replies View Related

Clear Range From All Worksheets

Jul 19, 2007

I have a macro to clear all contents for a sheet in a workbook. The problem is that every file has over 20 sheets of data. Is there a way I can code a for statement to clear all sheets in this one workbook? Is it possible to make a for statement for multiple workbooks with several sheets?


Range("A11:Z11").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents
Selection.Interior.ColorIndex = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.ClearComments

View 9 Replies View Related

Clear Variable Range

Aug 31, 2007

why the first version below works but the second doesn't

1st

ThisWorkbook.Worksheets("GBP%"). Range(Cells(8, c), Cells(z, c)).ClearContents

2nd

Worksheets("GBP%").Range(Cells(8, c), Cells(z, c)).ClearContents

c = 16 (uses a for statement to go through columns 16 to 111)
z = in this case 24, but is dynamic depending on number of files in a list

Currently using Windows 2000 with office 2000

View 6 Replies View Related

Clear Range On Another Sheet

Apr 18, 2008

I have used the VBA and it works to select all data but I have an issue when there is no data to select beneath the column header. I am running this on multiple worksheets which vary with data week to week.

Sheets("sheet1"). Range("A6:G" & Range("G" & Rows.Count).End(xlUp).Row).Offset(0, 0).Select selection.ClearContents

How can I run this and not have it delete the column header when there is no data? Is there a way to change the formula so that it selects all cell columns of data without defining it?

View 8 Replies View Related

Clear Set Range Variable

Jun 18, 2008

is it possible to clear the value in a range variable?

For example say:

Set A = Range("B4")

Is there a way to clear the value of that range, in other words make it blank again? And unfortuanlly i can't just make it equal to another range i need it to be blank.

View 6 Replies View Related

Count Last Clear Row In Column

Apr 1, 2014

I have the below code to set "u" as the number of the last row to start a loop function but it isn't quite right.

[Code] .......

View 3 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

Fast Way To Clear A Range Of Cells

Jul 8, 2009

This seems to take much longer than I would have expected. Is there anything I can do to speed it up, or is this just something to live with?

View 13 Replies View Related

Clear Contents Of Cells In A Range, LEN=0

Dec 23, 2009

I'm using a macro to copy the results of a formula and paste the values only on another sheet. The result includes lots of "blank" rows. I have another macro to get rid of the empty rows and move the information up.

It's not working because the "blank" rows aren't empty. Even though I paste values only, experimentation shows that the cells that appear blank return a false to the ISBLANK test with a length of 0.

So now I think I need a macro to run after the pastespecial command to look for cells within a range with a length of zero and delete the contents of those cells, but leave alone anything with a length of >0.

I am brand new to the idea of using VBA, but I have successfully cobbled together some stuff and can usually modify things to work.

It seems I need to maybe use some sort of IF statement along with a LEN and ClearContents. I don't want to delete the blank cells, just make them truly empty so that all of my actual data stays where it should, and my delete empty rows macro works correctly.

I did a search and see some info on clearing contents of columns or rows, or clearing contents based on the content of other columns or rows, but I'm unsure of how to tell it to search each cell within a range and clear the contents of 0-length cells to make them truly empty.

View 10 Replies View Related

Macro To Clear Colums In Range

Jun 19, 2009

I am trying to create a marco that clears the columns of a named range if the sum of the cells in the range is zero.

View 3 Replies View Related

Clear Range Based On Shading?

Dec 26, 2013

I need a macro that will clear the contents of all cells shaded white in range c1:c20.

Clear the contents, leave the cells shaded white.

View 1 Replies View Related

Clear Cells In Range That Are Not Numeric

Mar 28, 2007

I am trying to write a macro in excel to clear all cells within a range that are non numeric. I seem to be going round in circles trying to find out how to do this.

I assume I have to use the IsNotNumeric(Target) argument but I can't find how to specify the target within a range.

View 9 Replies View Related

How To Clear A Range Of Formula Quickly

Jul 13, 2007

I want to clear the range A10:IV65536 or all the cells containning numbers under the row A10.

Right now it takes for ever to clear it mannually because excel recalculates everything... i have over 100 000 data and id like to find a way to delete them quickly.

Is there a way to clear a range without excel recalculating everything???

If not the best way i think would be to clear the last columns first all the way to the first columns.

View 9 Replies View Related

Clear Contents Based Upon Value In Row Range

Jul 21, 2006

I'm trying clear the contents of a column range when the cell above this column range is empty. I know how to do this for one cell, but I would like it to work for a rangefrom R15:BB15.

If IsEmpty(Range("r15")) Then
Range("r16:r35").Select
Selection.ClearContents
End If

End Sub

View 5 Replies View Related

Clear/Delete Zeros In Specified Range

Aug 22, 2006

i need a macro that will select 0's in columns h-j.

View 7 Replies View Related

Clear Contents Of Filtered Range

Jan 10, 2007

I am filtering column S in a sheet to show all rows with a date after the end of the previous month - i.e. >= 01/01/07. What i want to do is clear the contents of those visible cells in column S. I tried the code below (got it on this site) but it works its way up from the bottom of the sheet until it finds the first visible row and then clears the contents of column S in each row above it, whether it is visible or not.

Sheets("Planning").Select
With Range("e2:C2")
.AutoFilter field:=5, Criteria1:="<=" & Sheets("Filtered Statistics").Range("c3")
.AutoFilter field:=19, Criteria1:=">=" & Sheets("Filtered Statistics").Range("d3")
For i = Range("s65536").End(xlUp).Row To 3 Step -1
If InStr(1, ">=" & Sheets("Filtered Statistics").Range("d3"), Cells(i, 19).Value) = 0 Then
Cells(i, 19).ClearContents
End If
Next i
.AutoFilter field:=19
End With

View 2 Replies View Related

Clear Named Range & Columns To Right

Sep 17, 2007

I have a dynamically named range the contents of which I want to clear. However, I also want to clear the cell contents in the next column beside the range. So if my dynamically named range is A20:B40 can I clear the contents of A20:C40 without changing the initially named range. Background: The data I have is the result of an advanced filter. The range is named for printing purposes without the final column.
If I then change one of the raw data I want to run the filter again. To do this I need to clear the first filter.

Range("AIT_Change_Management").ClearContents

View 7 Replies View Related

Clear Numeric Cells In Range

Jan 19, 2008

I am trying to code a command button to clear a range of cells if any of them contain a numeric value. For example. If any cells of cell range A1:C10 contain a numeric value then they would be cleared. Not all the cells in the range but only thouse contaiing numeric values. I have tried various methods with not-so-good results.

View 9 Replies View Related

Clear Range Using Variable Row Numbers

May 5, 2008

is it possible to refer to ranges by using index numbers?? I am looking for something like: (which I tried but does not work..)

Worksheets("Sheet1").Ranges("Cells(counter,1):Cells(counter+10,10)").ClearContents

View 4 Replies View Related

Clear Formulas In Specific Range

Jun 6, 2008

For various reasons I need to copy a the range "a2:s251" in the sheet "FWD Input" to another sheet but I need to remove any formulas.

To do this I have used the

Sheets("FWD Input").Range("A2:S251").SpecialCells(xlCellTypeFormulas).ClearContents

This works fine if there are formulas in that range, however if there aren't it gives me: " Run time error: 1004 No cells were found"

So I think I need some code that counts the number of cells in the range with formulae in them and either goes ahead if there are some or quits if there aren't.

View 3 Replies View Related







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