Delete Row Of Active Cell

Mar 26, 2008

I have a macro for deleting a row. I want iit to delete the row that I have selected, that is i if I mark cell B22 I want it to delete row 22. But it deletes the row under it, that is 23.

Sub Tabortrad()
Dim intRadnr As Integer
Dim intStartrad As Integer
Dim intSlutrad As Integer
intRadnr = ActiveCell.Row
intStartrad = ActiveSheet.Range("Första").Row + 1
intSlutrad = ActiveSheet.Range("Sista").Row - 2
If intRadnr < intStartrad Or intRadnr >= intSlutrad Then
MsgBox "Kan inte radera denna rad. Placera markören på en av bokföringsraderna mellan rad " & intStartrad & " och rad " & intSlutrad - 1 & "." & Chr(13) & "En ny rad kommer att infogas under den rad där markören står.", vbOKOnly, "Felaktig rad markerad".......................

View 2 Replies


ADVERTISEMENT

Delete N Rows From Active Cell

Jun 12, 2009

Is there a quick way to delete "N" rows from the active cell regardless of the content of the cells in those rows? I import text files into excel then frequently have to go through them and delete rows of nonimportant data.

For example if cell A57 is currently selected can I run a macro that will delete the next 10 rows?

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

Delete From Non Active Sheet

Nov 22, 2011

I am not sure why this code won't delete, I can't believe excel requires the sheet to be active to delete from. It will only work if the .activate line is included.

How can you delete from another worksheet without first switching to it?

Code:

'Sheets("sheet1").Activate
Sheets("sheet1").Range(Cells(row, col), Cells(row + 7, col)).Delete shift:=xlUp

View 3 Replies View Related

Delete Active Workbook

Oct 10, 2004

if it is possible to delete the current workbook that is open?

I'm thinking of using an add-in to delete the workbook i'm viewing

View 9 Replies View Related

Delete Sheets In Active Workbook

Nov 27, 2008

I want to create a macro to check through all sheets in an active work book, if a sheet is empty, then delete it?

View 2 Replies View Related

VBA - Delete Sheets In Active Workbook According To Combobox Value

May 8, 2014

That particular question has been solved, but now i need it to work with multiple values from combobox.

So for example,
if "Master" is selected in combobox3, it will delete sheets 7, 8
if "CSR" is selected in combobox3, it will delete sheets 1, 8
if "Original IND" is slected in combobox, it will delete sheets 1,7

The current code will work when "master" is selected, however i am getting compile errors when i select "CSR" or "Original IND"

WhenI select CSR, it does create a new wb for me (without sheets 1,8) correctly but i get a Run time error.

Run-time error '-2147417848 (80010108)': Automation error

The object invoked has disconnected from its clients.

When Original IND is selected, i get a compile error and it highlights 'Make the new workbook active newWBK.Activate

View 5 Replies View Related

Select Active Cells And Delete Entire Row

Oct 30, 2012

I have data that i import on a daily basis, the data can range from a couple of rows to thousands,

What I need to do using VBA is select the active cells, where cells in row ''G'' is blank then delete the entire row where that cell is.

View 3 Replies View Related

Delete Shapes But Not Active X Controls Box On Same Sheet In VB

Mar 12, 2013

I have an active x control command button on the same sheet as some shapes (lines,circles) which are generated from some parameters. I want to be able to delete the current shapes so I can plot the next shape. Below is the delete shapes part of the code, not sure how to complete the code.

Currently, the box is getting deleted as well as the shapes

Code:

Private Sub CommandButton1_Click()
Dim line1_Beginx As Single, line1_beginy As Single, line1_endx As Single, line1_endy As Single
Dim line2_Beginx As Single, line2_beginy As Single, line2_endx As Single, line2_endy As Single

[Code]....

'Calculate positions of line vertices for plots

line1_Beginx = 1000
line1_beginy = 1000
line1_endx = 1000 + CD_2
line1_endy = 1000

[Code]....

View 2 Replies View Related

Delete Specific Type Of Chart From Active Sheet

Feb 20, 2009

i'm trying to delete a specific type of chart from the active sheet using this macro:

View 2 Replies View Related

Select/Delete Worksheet Text Boxes Using VBA On Active Sheet

Nov 10, 2009

I could use some assistance in creating a macro that will delete all and only text boxes on the active sheet. Some text boxes will be empty, but I still wish for them all to be deleted. Through searching the forums I see a similiar code, but it deletes all shapes on the active sheet:

View 8 Replies View Related

Delete Rows That Contains Zero And Copy Active Sheet To New Sheet

Oct 2, 2009

I want DELETE the rows that contains 0 (zero or -) in column F (SALDO BRUTO), when I click the Button (Clear). And How I can copy from this sheet to a new sheet in this workbook, but the names of new sheet automatic rename to next date or 2, and next when I click the Button "Copy to New Sheet".

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

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

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

Open Worksheet With Cell E1 As Active Cell

Sep 21, 2012

I have a macro in the Worksheet module which begins as follows, however I would like the cursor to be in cell "E1" when the sheet is activated.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Address "$E$1" Or .Cells.Count > 1 Then Exit Sub

I'm sure that there is a way of achieving that but just can't find it?

View 3 Replies View Related

Cell Contents Dependent On Active Cell?

Nov 9, 2012

I have a list of items in cells b2 to z2. I want to display the contents of those cells in b2 dependent on what the active cell is. For example if the active cell is in column b I want a2 to show b2. If the active cell is in column C I want A2 to show C2, etc.

View 3 Replies View Related

Add Active Cell Data To Cell By Clicking

Jul 19, 2007

I want to automatically put information from several cells into one cell, when these cells are clicked. Let's say that A1:J8 is the range were a cell can be clicked and if a cell is clicked in that range the value of these cells should be put in cell A9. If possible I want to avoid private subs in a worksheet. I preffer to have a macro running, so I can decide when to stop this routine.

View 3 Replies View Related

Highlight Active Cell Only?

Jun 8, 2014

Need to have only active cell in in any worksheet highlighted or formatted in a different way than other cells

View 1 Replies View Related







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