Deleting Range Of Cell In Other Worksheet

Oct 2, 2011

How can i delete a range of cell in another worksheet 2? for example, i have a data in B1:F1 in sheet 2 and i want to put a button in sheet 1 which will delete the data in B1:F1 in sheet 2.

View 1 Replies


ADVERTISEMENT

Deleting Data In A Range Of Cells Based On If There Is Data In Cell Outside Range

Aug 1, 2013

I am attempting to make a range of cells' data be deleted if a cell outside of that range that had a name in it is no longer there. So if I have a list of names in cells A1:A6 and a range of data in cells D10:E20 that corresponds to a name in cell A2. If the name in A2 is deleted I would want the data in D10:E20 to be deleted.

View 9 Replies View Related

Worksheet_change: Macro In One Worksheet To Run When Any Cell (in A Given Range)on A Different Worksheet (dataentry) Is Updated

Mar 27, 2007

I want a macro in one worksheet to run when any cell (in a given range)on a different worksheet (dataentry) is updated. I have spent along time trying to make it work with no avail. The code I use to start my macro is as follows.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub

If Target(1, 1).Address = "dataentry!H5:IV72" Then
If Not Intersect(Target(1, 1), Range("dataentry!H5:IV72")) Is Nothing Then

On Error Resume Next
Application.EnableEvents = False

View 7 Replies View Related

Select Range In Worksheet Where Last Cell In Range Is Variable?

Jan 27, 2012

I am trying to write code to select a range in a worksheet where the last cell in the range is variable.

Sub DataTest()
Dim LastColumn As Integer
Dim LastRow As Long
Dim LastCell As range

[Code].....

View 8 Replies View Related

Forbid Deleting Certain Worksheet

Aug 29, 2013

In a module, I have the following

code: Public Ctl As CommandBarControl
Sub DelSht()
Set Ctl = Application.CommandBars.FindControl(ID:=847)
Ctl.OnAction = "MyDelSht"
End Sub
Sub ResSht()
Set Ctl = Application.CommandBars.FindControl(ID:=847)
Ctl.OnAction = ""
End Sub

[code]....

Where went wrong?

View 1 Replies View Related

Deleting Worksheet Causes #REF! In Formulas

Sep 4, 2006

I have a macro that imports a text file. I then have summary sheets that reference to the sheets with the imported text file details. This is a task I complete every month.

The next month I delete the sheets with the imported text file details before importing current month's details. But when I do that it screws up my references.

Here's an example of a reference with previous months' imported text file details:

= SUM('X'!$I$1:$I$3000)/2+SUM('Y'!$I$1:$I$3000)/2

When I delete the sheets with the imported text file details and import current month's details, my reference suddenly looks like this:

=SUM(#REF!$I$1:$I$3000)/2+SUM(#REF!$I$1:$I$3000)/2

What can I do to prevent the summary sheet from messing up my original references?

View 3 Replies View Related

Automatic Message Box When Deleting A Worksheet

Apr 11, 2007

I have been browsing the web trying to find a worksheet event that will automatically open a message box if the user tries to delete the sheet. Is this possible??

PS I do not want them to delete the sheet, so perhaps a message box with a a cancel button

View 9 Replies View Related

Error Message When Deleting Worksheet

Nov 6, 2008

How do you generate an error message that states "You are not authorized to delete this worksheet" any time a user attempts to delete the specified worksheet? This message also has to block the user for deleting the worksheet.

View 6 Replies View Related

Deleting Specific Rows In Worksheet

Nov 28, 2006

i need to create a macro that will delete entire rows based on certain criteria. here is an example of some of the worksheet:

US0017651060AMR_CORP CM 12C
US0017658669AMR_CORP PR 8C

US00211H1077ATC_ONLANE_INC CM 92M
US00211H2067ATC_ONLANE_INC PR 100N

US0038813079ACACIA_RESH CM 12C
US0038812089ACACIA_RESH CM 16C

CA00440P2017ACE_AVIATION CM 7C
CA00440P1027ACE_AVIATION CM 7C
US00440P3001ACE_AVIATION CM 100S

CA00756J1049ADVANTEX_MARK CM 43C
CA00756J3029ADVANTEX_MARK PR 100N

i want to create a macro that, when searching the third column of data (containing "CM" and "PR"), if a group of rows (between blank rows) do not contain at least one "PR", delete entire group of rows.

Therefore, the rows with "ACACIA_RESH" and "ACE_AVIATION" would be deleted from the worksheet.

View 9 Replies View Related

Avoid Deleting A Specific Worksheet

May 2, 2006

I have a workbook where everything is controlled from a single worksheet that I named "Start".

In the "Start" worksheet I have instructions and one command button. When this button is clicked, it will start the VBA code. The code will basically show a couple of userforms where the user will input some information and after the user clicks on a button on the last userform, the code will generate new worksheet and present the results in that new worksheet.

The user could do this as many times as he wants, each time creating a new worksheet or overwriting in an existing worksheet (except the "Start" worksheet). The user is able to delete any worksheets he want.

I need to write a code so that if the user tries to delete the "Start" worksheet, it will not let him/her because then there is no worksheet where I have the button to click to start the VBA code.

I tried protecting the workbook, but then it does not allow to add new worksheets either.

View 9 Replies View Related

Adding & Deleting Button On Worksheet

Oct 24, 2006

I am trying to create two macros. The first macro will add a button, and the second macro will delete the button. I am noticing that when I add the button in the first macro, Excel is automatically naming the button (i.e. Button 26), and then when I try to delete the button with the second macro, this button name is not recognized. How can I name or set the button as a variable so that I can successfully run these two macros?

View 6 Replies View Related

Filtering & Deleting Non Numerical Values Throughout Worksheet

May 11, 2006

how to find a certain criteria and delete the entire row that the criteria is in. I am more interested in finding certain criteria with vba, in my case any text/non numerical values and clear contents from that cell for the entire worksheet while retaining the rest of the cells that have numerical values in them. My data has --- in cells that represents missing data which would be easier if it was completely blank.

View 2 Replies View Related

Suppress Confirmation Alert When Deleting Worksheet

Feb 6, 2008

we have a recorded to delete a sheet, then how to make sure the interactive box doesnt come up asking you to "delete" or "cancel"? And the sheets still get deleted?

Here's my
Sheets("regress").Select
ActiveSheet.Delete

How to make sure that the "Delete/Cancel" box doesnt appear?

View 9 Replies View Related

Adding And Deleting Rows In Worksheet While Maintaining Formulas?

Mar 26, 2014

I have a Productivity Report that contains very basic formulas that provide totals for 4 columns (B6:E6) and an average for one column (F). I have included two command buttons, one to add a new row and the other to delete a row.

I need to be able to add or delete rows depending on how many employees' productivity I will be tracking on any given week; each row represents a separate employee. I need the following functionality out of my form:

1) formula in column F needs to copy and paste with each new line
2) when a new line is copied and pasted I need the contents to be cleared
3) I need the user to be blocked from deleting the first row (3 on this form) in the table

The code I'm using for my "Add" button is:

[Code].....

The code I'm using for my "Delete" button is:

[Code] .....
The buttons add and delete rows as I'd like them to but content is not clearing, with each row added the contents provide a sub total. I've tried various lines of code (some more complicated and some less) before I recorded my own macro (see above).

Attached File : Productivity Report (HH).xlsm‎

View 8 Replies View Related

Deleting Workbook Based On Criteria In Active Worksheet?

Aug 16, 2012

I am trying to delete a workbook, yes the whole workbook, if cell A1 is blank.

View 2 Replies View Related

Deleting Multiple Worksheets And Saving Active Worksheet

Aug 3, 2007

I am trying to find a easy way to delete multiple worksheets in a workbook and then saving the workbook based on the tab name of the worksheet.

View 9 Replies View Related

Stop Deleting Formula Cells Without Worksheet Protection

Sep 12, 2006

I'm protecting a spreadsheet with Excel 2000. For that i used the Excel functionality : Data -> " Validation"

Unfortunately, I can not protect the all sheet or workbook (Requirement). So even if the cells are protected by "Validation", any user can delete the cells using the key DELETE or BACKSPACE on the keyboard !

VBA Macro (which could be activate at each change on the sheet for example...) ? or if it's possible to avoid cells deleting with the "Validation" in the Excel Data menu ?

If it's a macro, it would do :

1 ) see if a user is pushing DELETE or BACKSPACE on the keyboard

2 ) see if the cell contains formulas

3 ) if yes, make a box appears to say that is not possible to delete this cell

4 ) return on the Excel sheet without changes

View 9 Replies View Related

Sum Cell Range On Specified Worksheet

Aug 29, 2009

I have the following line of VBA code that sums a range of cells on the active worksheet. I need to change that to sum on a specified worksheet. Is there a way to do that? I thought it would be simple, but have had no luck... Actually using variables for the cells, but simplified here for example's sake.

If Application.WorksheetFunction.Sum(Range(Cells(2, 2), Cells(123, 2))) = 0

View 9 Replies View Related

Deleting A Defined Range Name?

Feb 17, 2010

Say i have a range A1:B3 and i define it as "Fruit", but then later decide i want to call it "food", or even just revert back to cell numbers. How would one go about doing this?

View 5 Replies View Related

Deleting Objects In A Range

Aug 24, 2007

I'm trying to remove gif, jpg, and xls objects from a specific range and I can't seem to figure out how to do it. I can remove all objects from the sheet or only the gif and jpg files from a range. Here is the code I've been playing with:

Sub Clear_Sigs()
Dim Sh As Shape
With Worksheets("Sheet1")
For Each Sh In .Shapes
If Not Application.Intersect(Sh.TopLeftCell, .Range("A26:E32")) Is Nothing Then
If Sh.Type = msoPicture Then Sh.Delete
End If
Next Sh
End With
End Sub

I know this code says to delete only pictures and I've tried tweeking it to do all objects but it doesn't work for just the range I want. Any help is appreciated.

Thanks,
Amy

View 9 Replies View Related

Deleting Range Of Cells Using VBA

Jun 12, 2009

I need to have my macro select a certain range of cells with a specific product code and delete them. What would be the code for it (product code 107). Here is what i have so far:

Workbooks.Open Filename:=fNameAndPath, UpdateLinks:=1
ActiveWorkbook.Save
ActiveWorkbook.SaveAs Filename:=myStrPath1 & myStrPath2 & myFile3 & myExt, FileFormat:=xlNormal, CreateBackup:=False
Windows(myFile3).Activate
Cells.Select.Range ("A78:K153")
Selection.Delete
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.SaveAs Filename:=myStrPath1 & myStrPath4 & myFile4 & myExt2, FileFormat:=xlCSV, CreateBackup:=False

View 9 Replies View Related

Use If / Then Statement - Deleting Range Of Cells

Jun 11, 2014

I have a sheet with thousands of rows and 45 columns and I need to delete a range of cells (G:Y) if the cell in column V = the cell in column X, and I need to do this by row. I think an if/then statement would work.

View 4 Replies View Related

Deleting Duplicates In Range Of Rows?

Mar 22, 2013

I have the following problem within Excel. I have a dataset which contains duplicate values within a column(lets say A). I need to delete these duplicates in column A for a range of rows, where the range of the rows is based on column B. So for a given value in column B, lets say 5 which is 6 rows long all beneath each other, I need to delete the duplicates in column A.

A B
1 2234
4 2234
5 2234
4 2234
2 2234
3 2234
5 2234
5 2657
8 2657
9 2657
10 2657
8 2657
9 2657
2 2657

So above the red numbers need to be removed, automatically, since we have about 8000 rows in our dataset.

View 7 Replies View Related

Deleting Particular Sheet / Range Or Selection

Oct 2, 2012

Any easy way of finding out if deleting a particular sheet / range or selection will mess up any references elsewhere in the workbook?

View 1 Replies View Related

Deleting Sheets Based On A Range

Nov 13, 2013

I'm pretty new to coding from scratch in VB, and I've got some code that should loop, but it doesn't. The idea eventually will be to download a spreadsheet, make a new sheet for every item in a range, and then filter for each of those items and put it in the right sheet. I have to code to create the sheets (lightly modified from something I got here)

Sub CreateSheets()
'Written by Barrie Davidson
For Each c In Sheets("FilterList").Range("b2:b74")
Sheets.Add
ActiveSheet.Name = Right(c.Value, 30)
Next c
End Sub

That's working well, so I've tried to modify it so that it will also delete sheets based on the same range. I've gotten it to delete the first item in the range, but then it stops.

For Each c In Sheets("FilterList").Range("b2:b74")
Application.DisplayAlerts = False
Worksheets(c.Value).Delete
ActiveSheet.Name = Right(c.Value, 30)
Next c
End Sub

By the way, I don't know what the ActiveSheet.Name = Right(c.Value, 30) line does, but I put it in since it was in the code I was modifying.

View 9 Replies View Related

Save Worksheet As PDF And As Specific Cell Range?

Mar 26, 2014

I have a macro assigned to a button that saves the worksheet as a PDF in a certain folder, but what I cant do is make the Macro look at a certain cell to save the file as a specific batch number.

This is the macro I have so far, I want the macro to do as below but to also look at cell L3 in the same worksheet and save it as the number what is in that cell.

[Code] ......

View 1 Replies View Related

Highlighting/Deleting Special Charahters In A Range

Aug 15, 2008

I have a column that needs to be text format, but it needs to include only digits (0-9), no letters, no special characters. Also all cells need to have 7 digits.

I am trying to do a couple of things:
1 : Hightlight the cells that contain Strings with Non-Digits characters
2 : Highlight the cells with less than 7 characters (I have the code, it is below)

I have tried many ways to get #1 but I am stuck.

Help please.

This highlights the cells with less than 7 characters:

Sub StringLength3() ' IT WORKS!

Dim strTest As String
Dim i As Integer
Dim Cell As Range

'Select Range
Worksheets("tres").Activate
Worksheets("tres").Range("A2", Range("a65536").End(xlUp)).Name = "RangeA"
Range("RangeA").Select

For Each Rangea In Selection
i = Len(Rangea)
If i 7 Then
Rangea.Cells.Interior.ColorIndex = 7
End If

Next Rangea

End Sub

View 9 Replies View Related

Find Text In Workbook And Then Deleting Range Of Cells Above It

Jan 29, 2014

I want to create a macro that will find a text string called "Season Average" in the entire workbook and delete a range of cells above it 4 cells longs starting right above the text and then going left.

Example is if "Season Average" is in cell D11, than I want the cells D10, C10, B10 & A10 Deleted and the cells to shift up

This happens throughout the entire workbook and multiple times on each work sheet in different spots not consistently same cells.

View 9 Replies View Related

Deleting / Clearing Multiple Cells In Target Range

Jun 2, 2014

I have a target range for a worksheet change. Then when finished I highlight the data and press 'delete' I get an error within the code.

View 2 Replies View Related

Error Deleting Range Name In Kanji (Japanese) Language

Sep 8, 2006

I have users that submit a workbook quarterly. Many of the submissions contain name ranges that I need to remove. I wrote the below to delete the named ranges but encounter an error when the macro encounters a named range with the Kanji (Japanese) character set. How can I update this to delete these ranges as well?

Sub DeleteNamedRanges()
Dim ws As Worksheet
Dim NR As Name

On Error Goto ErrTrap

For Each ws In ActiveWorkbook.Worksheets
ws.Activate
For Each NR In ActiveWorkbook.Names
NR.Delete
Next
Next

ErrTrap:

If Err.Number = "1004" Then
Resume Next
End If

End Sub

View 9 Replies View Related







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