Deleting Row At Active Cell

Nov 8, 2013

Creating macros in excel. I am looking for a way to delete a row at the active cell. So far this code does the job for me:

Code:

Sub slet()
ActiveSheet.Unprotect
ActiveCell.EntireRow.Delete
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True
End Sub

But I want to make it impossible to use this code on the first 4 rows.

View 3 Replies


ADVERTISEMENT

Deleting An Active Control Along With The Cell

Jul 10, 2009

I have created a spreadsheet that uses checkboxes. When deleting a particular row that is no longer needed, it leaves the check boxes. Is there a way to link them to the cell (which contain formulas) so when the row is deleted, I don't have to delete each individual check box.

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

Excel 2003 :: Highlight Cell On Same Row As Active.cell Whenever Active.cell Changes

Dec 10, 2012

whenever the active cell is within a given range, highlight the cell on the same row in column S (by changing its interior colour). This should occur each time the active cell is changed, whether by cursor keys or mouse. The effect would be similar to the row and column highlights at left and top of the worksheet.

This action should be restricted to one sheet in the workbook.

It's for Excel 2003.

View 3 Replies View Related

Selecting Range From Active Cell To Last Entry In Same Row As Active Cell

Jun 8, 2014

I have got stuck on one piece of my code and having trouble fixing... Overall I am trying to find variable station name in cell L2 of Sheet 2 in Sheet 1 and then select and copy the data from the data in "cell L2 of Sheet 2" to the last entry of that row. I have attached an example test spreadsheet of the data and a macro is within Sheet 1 called test1. Please note that cell L2 in Sheet 2 will always be different station name and the station list in Sheet 1 will change with differing station name.

The code I am using is:

[Code] .....

The code that is not working and bringing up an error is:

[Code] .....

Attached File : Copy of Testexample.xlsm‎

View 2 Replies View Related

Find Method To Search For The Active And Non Active Values

Jul 14, 2009

I have a range of amounts in Sheet 1 from F7:Q13 and im using the find method to search for the active and non active values in the cell. Which means that if there's a value in the cell it will transfer the value in Sheet 2, if nothing is found in the cell the cells in Sheet 2 will return as nothing or null.

I think the problem lies on the FindWhat variable. Im getting a compiled error which im not sure what is it.

I've attached the spreadsheet so you get a better idea of the problem that i encountered.

View 13 Replies View Related

Macro With Reference To Active Chart On Active Sheet

May 14, 2014

I currently have the following macro running to set a chart's data values:

Sub C3Quarter12013()
'
' C3Quarter32013 Macro
'
'
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Values = _

[Code] ......

When I copy the tab and change some of the data within the cells, I want the macro refer to the chart on the current tab and the values in the current tab - as currently it refers to only "Chart 2" and the values in the tab 'Figure 2 - WE OPH'.

I've tried changing the sheet name to ActiveSheet.name but that doesn't seem to work.

View 4 Replies View Related

VBA - Selecting Active Cell And Working Back To Specific Cell Range?

Jul 2, 2014

I have a form that loads and depending on the word selected in the drop down the following code loops through cells Q2:AC2 until it finds the word in one of those cells (the word will always be in one of the cells)

Code:
For Each c In Range("Q2:AC2").Cells
If c = period Then
c.Select

[Code]...

The active cell it finds will always change, i know I need something to code the active cell back but I don't know what it should be.

View 5 Replies View Related

Assign Active Cell In Macro With Dynamic Cell Choice

Dec 29, 2009

Before unloading a userform the range to select the active cell is set to

View 3 Replies View Related

Deleting Cell Content Based On Entry In One Cell?

Mar 22, 2014

I have a sheet that I fill out with customer data then print and start over with the next customer. This requires me to tab and delete through the sheet before starting the next entry and I am wondering if there is some way to auto clear the unlocked cells based on a single entry IE when we entered new data in the 1st field this would clear the unlocked cells and make them ready for new data?

View 14 Replies View Related

Formula With Offset From Active Cell And Sum Until Blank Cell

Dec 24, 2013

I have a formula in cel B1 : =SUM(A2:A100) / A1

I would like to use this formula many times in the sheet, so I would need a reference to the cell the formula is in, and have the SUM range until the next empty cell one column to the left.

So I would need something like (literally):

=SUM(Offset activecell (1,-1) : Offset activecell (1, (look for next empty cell -1)) / Offset activecell (0,-1)

View 3 Replies View Related

Identify Active Cell And Use The Column To Add Formula To Another Cell

Dec 1, 2009

I have a range of unlocked cells (B5:S10) that users enter data in. This sum of this data is then charted. The formula (sum) in a cell equals zero even when there is no data entered by the user. This zero is then charted.

I need to be able to plot the zeros if the user enters zeros but not plot the zero if the cells are blank.

What I was attempting to do is to use the worksheet change event to add the formulas to a cell so that the chart does not plot the value until something was added.

In my change event I need to know that a cell in the range (B5:S10) was changed and that if it was D7 (for example) that I need a formula enterd in D11 [=SUM(D5:D10)]. If it was I5 then the formula would have to go in I11 [=SUM(I5:I10)].

View 8 Replies View Related

Changing Active Cell To Multiple Cell Range

Feb 24, 2012

I'd like to ranage the range selection from D1 to A1:B53, but a number of attempts have failed. but can't get it working.

Code:

FileName = "R" + Scheme + Product + "_" + Format((Date + 1), "DDMMYY") + ".RP3.txt"
Sheets("RP3").Select
Range("D1").Select
WholeText = ActiveCell.Value
Call OutPutFile(WholeText, FileName)

View 1 Replies View Related

VBA - Find Cell Containing Variable - First Result Above Active Cell?

Dec 26, 2012

Need to find the first cell above the active cell containing any sort of variable (integer, string value, cell fill colour, named range, etc...).

For example, a column contains multiple integer values between 1 and 10. I would like the macro to "locate" a specified value, say 3. Ideally the macro will select the first cell matching this criteria, located above the active cell. Once located, the resulting cell will be added to a range to be copy-pasted to a new worksheet.

I am planning to use this code for various applications in a workbook I am creating. At present the code will be used to locate string values, and cell fill colours. It is also likely this will extend to other types of variables in the future.

I've found information relating to the find function, but nothing specifically to find the first result above the active cell.

View 5 Replies View Related

Move To A Cell Depending On Variable Active Cell

Jul 23, 2013

So, I just started checking Excel Macros. I'm working on a quite large data base where I need to search for a given part number and then move N columns to the right and display the text inside that cell on a message box.

Here's what I got 'til now:

Private Sub M0016216_Command_Button_Click()
Cells.Find(What:="M0016216", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

ActiveCell.Select

[Code] ........

View 3 Replies View Related

Go To First Cell In List That Matches Contents Of Active Cell

Feb 20, 2007

I am just learning to use VBA and this may be the most simple task ever, but I can't figure it out. I've searched for all of the keywords I can think of, but can't find a solution...

I have a list of names in a sheet. Other columns in this sheet contain data like amount charged, amount paid, etc. This sheet must be manually updated (because the other program won't export the information I need) periodically to ensure proper billing/payment application in the original software (all transactions are handled by other people that I don't trust).

I sort the list so that the all names that are the same (ie John Doe) are together.

A short example list looks like this:

Jeremy Apple
John Doe
John Doe
John Doe
Jimmy Kravitz
Jimmy Kravitz

In updating my sheet, I set up a macro that will input todays date in one of the columns for all occurences of that name (so, every row that contains John Doe in column B, column V will have todays date in it).

Currently, in order for my macro to work properly, I have to manually make the activecell the first occurrence of 'John Doe'. When I'm ready to update 'Jimmy Kravitz', I have to select the first occurrence of 'Jimmy Kravitz' and so on.

Here's my question - Is there a way to use a VBA macro to find the first occurrence of 'John Doe' (and automatically 'know' which name I am updating)? Basically, I need a macro that will take the information that is in the cell in column B in the active row, find the first row that has that same name, and make that cell (column B) the active cell...

View 9 Replies View Related

Identify Cell Address Of Active Cell Selection

Oct 10, 2007

I have an embedded chart on my worksheet.I can select a cell behind the chart using the keyboard arrow keys.Is there a way of doing this using a mouse click,so that I know which cell i am pointing to/choosing?

View 4 Replies View Related

Return Maximum Of Active Cell & Arbitrary Value To Cell

Nov 18, 2007

I am looking for VBA that will add the value of the current active cell on the sheet to the value in cell F12. The maximum value of F12 cannot exceed 1000. So if the value in F12 = 950 and 100 is the value in the active cell the maximum value in F12 should show 1000, not 1050.

It should do this on the click of a button.

View 5 Replies View Related

Deleting Row/cell

Mar 31, 2007

In Excel 2007, when I delete a row/cell, text is associated with deleting row/cell but some lines, polygon ... can not associate with deleting row/cell . So the lines, polygon ... in mistake location.

View 12 Replies View Related

Deleting Value From A Cell

Oct 8, 2008

I am using this code to populate column J with a date when yes is selected in colum I.

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column = Columns("I").Column Then
If .Value = "Yes" And IsEmpty(.Offset(, 1)) Then .Offset(, 1) = Date
End If
End With
End Sub

However i also want any value entered in J to be deleted when No is selected.

View 2 Replies View Related

Deleting Third Value In Cell

Sep 21, 2006

My original datasheet had two separate columns, First Name & Last Name. I've combined the two columns into one so it appears Last Name, First Name. Some of the names have middle names. Is there anyway to delete the middle name.

Example: Cope, David Simon

Trying to get: Cope, David

View 7 Replies View Related

Use Value Of Active Cell To Name Row

Mar 27, 2007

I'm trying to take the value of a cell and use the value as a name for the row.
If cell a1 has value = June. I want to change the name of row 1 to June.
I'm not sure what I'm doing wrong with the following code.

Sub Name_a_row()
'
'
Dim TheName As String
Dim RowNum As Integer
TheName = ActiveCell.Value
RowNum = ActiveCell.Row
ActiveWorkbook.Names.Add Name:="TheName", RefersToR1C1:="=Data!R&RowNum"

View 5 Replies View Related

Cannot Delete A Cell Without Deleting A Row

May 23, 2006

I am using Excel on a work computer that someone else may have changed the settings on.

I want to simply delete a cell, without deleting the entire row the cell belongs to. Yet when I highlight the cell, and go to Edit -> Delete, the program asks me if I want to delete the entire row, and only gives me two options: OK or Cancel. So I can't delete just one cell (and have all of the lower cells shift up one).

When I right click, the 'delete' option is not present either. I tried changing the menu properties to add the 'delete' function, but it will not delete a cell without taking out the whole row.

View 3 Replies View Related

Deleting Incorrect Cell Name?

Jun 24, 2014

I have a lot of calculations going on so I thought I would name some of the cells where factors come from to make it easier to follow.

I made a typo and named a cell wrong, is there a way to delete it? Right now I have a correct and incorrect name attached to the cell.

View 3 Replies View Related

Deleting Last Cell In A Specific Row

Feb 24, 2009

hey everyone

i'm using a table like this one :

A B C D

1 Main | value | value | value
2 Extra | value | value | value
3 Main | value | value | value
.
.
n Extra | value | value | value

i want to delete every cell in column "D" only when cell in column "A" contains "Extra" value.

View 6 Replies View Related

Deleting Columns Where A Cell Within Contains A Value

Jan 28, 2010

I want to delete columns that contain a 0 value in row 2 of the column.

In my case, I have headers in row 1 and values in rows 2 and below. I eventually want to filter out the columns that contain no values below the headers.

Let me know if you need any more information for clarification.

View 10 Replies View Related

Deleting A Cell When Linked?

Oct 27, 2011

I was wondering if ther is a way to delete what is in one cell, once something has been enterted in another?

Example A1 has In-progress written in the cell.

Once B1 has Completed.

So once B1 has been entered, the info in cell A1 will be deleted.

View 4 Replies View Related

Finding A Value In A Cell And Deleting A Row

Feb 10, 2009

I'm looking to do a search and delete in Excel 2007 and I'm having a great deal of difficulty trying to do this. I've attempted to modify some code I found on the internet and not having very good luck with it. Here is the scenario, I've got a spreadsheet with 5 columns (A to E). In column C, there is a product name with certain identifiers that set it apart. An example of this product name with identifier is "product XXX_type 2_attribute ". I want to search for "type 2" in Column C of each row and then delete the row.

Here is what I have written so far and I'm not having any luck.

Sub RowDel()
Dim cell As Range
For Each cell In Range(Range("c4"), _
Range("c65536").End(xlUp))
If cell = "_GPnl_" Then
Range(cell, _
Cells(Rows.Count, 1)).EntireRow.Delete
Exit For
End If
Next cell
End Sub

View 9 Replies View Related

Deleting Part Of A Cell

Apr 5, 2002

I want to delete part of a cell in Excel. For example if a cell had the data "BlueGreen" how would I delete the "Blue" bit just to leave the "Green" bit behind? Surely there is an easier way then physically deleting the first part in each cell? I have about 4000 cells to work on!

View 9 Replies View Related







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