Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Delete Row Of Active Cell


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 Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Insert Row On Sheet & Move Active Cell Row To It
I would like to create a macro that could archive entries from one sheet and insert them in another. I created one but the problem is that the entry has to be the same row each time.

Example:

Sheet 1 – is current jobs and sheet 2 is old jobs.

My macro moves an entry from Row A-5 of Sheet 1 and moves it to the top of Sheet 2.

I would like to be able to scroll through each entry select it and have it moved to the top of the Old Jobs sheet.

View Replies!   View Related
Highlighting Active Cell's Row, Along With Any Row That Shares Same Value In That Column
Is it possible to click on a cell in column C, and have the wishlist below happen:

That active cell's row is hightlighted.

Any cell in that column that has the same value as active cell is also highlighted.

Plus, any cell in another sheet that has that value it's row is highlighted too.

Example:

I click on C5 in Sheet 2 its value is 45000789 it row is highlighted, this value also appears in C3 in the same sheet, so it's row is highlighted as well. Plus, in sheet 1 in C10 this value appears and it's row is highlighted as well. When any of the values are clicked again the highlight is removed from all parties.

View Replies!   View Related
Delete N Rows From Active Cell
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 Replies!   View Related
Insert Row At Active Cell With Formula From Fixed Row
I want to insert a new row that contains the formulas of a fixed row (1:1).
The inserted row is changeable and is determined by whichever is the current active cell.

Eg:

Active cell is something random like E16

I want to add a new row but don't want a blank row - rather want a row that contains the properties of 1:1

View Replies!   View Related
Use Value Of Active Cell To Name Row
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 Replies!   View Related
Row Of Active Cell Selected
I know how to select an entire row, but if I want the "row" with active cell to be highlighted when I press enter, or the down arrow key, is there a way to do that (mainly for ease of viewing).


View Replies!   View Related
Use Active Cell For Row Number
There is a chart encompasing column A, B and C. The column D has certain numbers stored in its cells. All I wont is to build a code which would check the value of the cell in column D which is in the same row as the active cell, and then paste certain date into the active cell - basing on the value of the cell in column D.

Here an example:

Sub pasteif()
first one) column D has
'if the value is i.e 2 then it should paste into the active cell whatever there is in Range F1:G1
'if other value is present in this case in cell D1 (it is always the D column) then other range would copied into the active cell
Range("F1:G1").Copy
ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

View Replies!   View Related
Row Number Of Active Cell
I need to write code which would find the row number of my selected range (named 's')and then select the row beneath that.

View Replies!   View Related
Highlight Row Of Active Cell
Is it possible to have a specified shading (say 50%) applied to all rows except the currently picked row and the header rows to allow a user to focus on inputting across the row? I'd use this in conjunction with " Move Selection After Enter" to "Right" so the user would stay on the same row. I've tried the Help function, but can't find anything.

View Replies!   View Related
Whole Row Of Active Cell Should Be Bold
The text of the active cell's row should be displayed BOLD. In other words, My cursor is on row 8, then this whole row should be bold. Is this possible ?

View Replies!   View Related
Select Row For Active Cell
I need to select the row for the active cell without using an explicit RC address. I don't seem to be able to make the syntax work.

View Replies!   View Related
Range In Same Row As Active Cell
I'm looking for a piece of code, which would activate a certain Range i.e. the start of which would be in column A and the End in Column G. My problem is that the activated range of cells shuld be exactly in the same row as the currently active cell i.e. active cell B3 -> activated range A3:G3 .

View Replies!   View Related
Moving Active Cell In Same Row To Column A
In my macro, I need to, from any column, move the active cell to column A, while remaining in the same row.

View Replies!   View Related
Macro To Select The Row That The Active Cell Is In
Using vba how do I tell a macro to select the row that the active cell is in?

I'm just using a basic delete Row macro but I'd like for the macro to automatically select the entire row when it's time to delete instead of me highlighting the section.

View Replies!   View Related
Highlite Or Underline Active Cell Row
Is there any way in excel to allow for whenever clicking on an active cell to make an underline or highlite the entire row that that cell is in?

View Replies!   View Related
Use Active Cell Row To Check Another Column
I've been racking my brains trying to figure this out but cant, please can someone assist. I need to write some vba code that looks at cells in column aa and ag (on the same row) and displays a message is ag > 0 and aa <> "Agency".

View Replies!   View Related
Offset Active Cell To Beginning Of Row
I need to make the active cell go to the beginning of the row (Column A). I tried setting up a variable to be the row.value, but that didn't work (the way I wrote it, which doesn't surprise me).

View Replies!   View Related
Return Active Cell's Location/row
I'm having trouble identifing a way to return a location for the position of the active cell. I've searched Excel help with "Position, location, return, activecell, etc." and I can't seem to figure this out. I know that it's possible, so that's why I'm on here!

...

Ok, say the active cell is currently "F1", and I need the location "F1" to identify the ROW to be used in a formula later, how would I go about that?

The current contents of cell "F1"' will be "REPLACE", but I need to change the words "REPLACE" in "F1" and other cells labeled "REPLACE" in column F to the following formula (where the "1" in "A1" is is the current row):

View Replies!   View Related
Merge Range Based On Active Cell Row
I am working on a macros that creates a new row for every data entry. Below is the macros that I have. In the new row, I want for the cells in columns F through O to merge right after creating the row. How do I go about this?

If Sigma = 0 Then

Selection.EntireRow.Insert ' New row for new entries
ActiveCell.Value = "NONE"
ActiveCell.Offset(1, 0).Select
End If

View Replies!   View Related
Determine Row Number Of Active Cell In CurrentRegion
I'm trying identify the active cell row number in a current region for viRows below. Currently I'm getting the active cell row number on the total worksheet.

With ActiveCell.CurrentRegion
viCols = .Columns.Count
viRows = .Range(ActiveCell.Row).Count
End With

View Replies!   View Related
Set Variable To Row & Active Cell Column
I have a sort procedure I have been working on. Sort By Active Cell Column
Now I would like to make sure the row of the activecell.column is row 7. I tried

Private Sub comp_myMonthlyReport_SortAscend()
Dim rng As Range
With ActiveWindow
rng = .ActiveCell(7, .ActiveCell.Column)
End With
Selection.Sort Key1:=rng, _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

But I receive this error: Run-time error '91': Object variable or With block variable not set

View Replies!   View Related
Fill UserForms Controls Based On Active Cell/Row/Column
I am attempting to write an Excel Macro that would allow me to Click on a particular row and have the fields in my form fill with the data in the columns of the row.

I am fairly new to Macros and VBA, but know that this has got to be a possibility.


View Replies!   View Related
Pick Data From A Specific Row/column (eg 10/B) Related To Active Cell
I have a spreadsheet with my Periods along row 10. e.g. C10: "1", D10: "2", E10 "3", F10: "4", G10: "5" etc. (green on the attached sheet). I have my departments along column B, e.g. B11: "Baked" B12: "Fresh" B13: "Frozen" (yellow on the attached sheet)

what I need and cannot work out is some VBA code that will populate two variables (lets call them Period & Department) when I click on one of the figures. For example if I click on cell: if I click E14: Period would have the contents of cell E10, and Department the contents of cell B14.

if i click G14: Period would have the contents of G10, and Department the contents of cell B14 again. I know how to get the click on the cell to work properly etc, and I have code to slot these variables into that works very nicely, I just can't get this bit to work!!!!

View Replies!   View Related
Search For Text Delete This Row And Delete The Row For Every Instance
I have an formula if statement that returns "deletethisrow" if the test is true.

For every occurence of "deletethisrow" I want to delete the row. The number instances will be variable each time I run the file. So maybe it will find that string, maybe it will find 10 instances. I want to do some kind of loop that won't error out when it cannot find "deletethisrow", but will delete the rows for each instance where it does find this string.

I know it was verbose, but if I just do a loop for a fixed number of loops it will error out if it runs out of rows to delete.

View Replies!   View Related
If Cell Contains Value, Delete Whole Row
I am writing on a macro to get some documents in order, but need some assistance in cleaning up please.

I would like to have a macro which deletes me the whole ROW if the cell in this row DOES NOT contain the words "V1", "V2" or "V3" (without "). ATTENTION! The cell does not only contain V1, V2, V3, it contains several words and these might be one of them, hence I need the macro the check the cells content for these values.

Example:

Column A
1. I want a V1 setup
2. I want a hamburger
3. I want a V5 setup
4. I need a dog

The macro should delete rows 2,3,4, only row one remains as it is.

However, I would need to be able to define the range of the checked cells, since I dont want to check the whole document, merely a part of it.

View Replies!   View Related
Delete Row If Cell Contains A Z
I need to delete a row if one of the cells in that row contains a Z but not the ones that contain a ZC# (i.e. ZC5, ZC6).

For example.. the row that contains ADH-15-STD ZKPINTC1-2500 needs to be deleted.

View Replies!   View Related
Delete Row If Cell Contains
I need to know the VBA code for how to delete any row that contains the text "Adbank Standard". This can appear in various columns , is this possible?

I know I can do it via a filter yet need it in VBA for a macro


View Replies!   View Related
If Cell Contains Certain Value, Delete Row
My current macro will sort column A in ascending order, then I wih to wipe out some of the rows. Starting at row 511, you will see there are values that say "znot assigned".

What can be written so that if a value in column A reads "znot assigned", it will delete the whole row?

View Replies!   View Related
Delete Active Workbook
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 Replies!   View Related
VB Code - Cell(Lx)= (x) Then Delete Row (x)
Can anyone help me here? I'm in need of a VB Code.

I need the code to do the following:

If any cell in column "L" = "n/a" then delete that corresponding row.

IE - Cell L23 = n/a so then delete row 23.

View Replies!   View Related
Check Value Of Cell, And Delete Row
a code that can check the values of a row, and if it is equal to zero, or has no value in the cell, then delete the entire row. Let me know if you need more info or anything.

View Replies!   View Related
Delete Row If Cell Value Is Not Number
I search the board and found this VB. Is doing what I want, which is deleting any rows for which the cell in coulum E is not a number. But also is erasing the content of colum D.
________________
Sub DelTxtCell()
On Error Resume Next
With Range("e1", Range("e" & Rows.Count).End(xlUp)).Offset(, -1)
.Formula = "=if(isnumber(left(e1,1)+0),"""",false)"
.SpecialCells(-4123, 4).EntireRow.Delete
End With

End Sub
________________

I tried deleting the Offset and the SpecialCells; but as you can see by now, I do not know what I am doing.

View Replies!   View Related
Delete Row If Cell Is Empty
I have the below code :

Private Sub worksheet_change(ByVal target As Range)
Application.EnableEvents = False

finalrow = Cells(65536, 2).End(xlUp).Row
For i = 5 To finalrow
If Cells(i, 2).Value = "1" Then
Range("C3:J3").copy
Cells(i, 3).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End If
Next i

Application.EnableEvents = True
Application.CutCopyMode = False
End Sub

It baiscally copies row C3:J3 and pastes it in the row range "C:J" depending on where "1" is placed in column B. Code works fine, I want to add another loop in the code which deletes the entire row if the cells in column "B" do not equal "1".

View Replies!   View Related
Delete Row If Cell Equals
I am familiar with the macro DeleteRowOnCell, but I want to delete rows when a cell in a selected column has a formulae which is returning empty text. I tried the macro DeleteRowsFastest without success.

View Replies!   View Related
Delete Row Based On Cell Below
I currently have a spreadsheet with circa 50,000 lines with info regarding our websites at work. In every row, column "A" contains a unique reference number.There eill either be 1 row for this reference number, or there will be 2 rows. there will never be more than 2 rows for each reference. I wanted some code to say that if theres only one row, then delete it but if the if the cell underneath it is the same then its ok. I tried the following but it deletes the row anyway, supposing the cell under it is the same or not

Sub test()
Dim icell
icell = ActiveCell.Offset(1, 0).Value
Range("A1").Select
Do
If ActiveCell.Value = icell Then
ActiveCell.Offset(2, 0).Select
Else
ActiveCell.Rows.Delete
End If
Loop Until ActiveCell.Value = ""
End Sub

View Replies!   View Related
Delete Row When Cell Not Equal To Another
The code below is an attempt to delete all rows where the cell in column A is not equal to the value of B1. B1 is text if that somehow makes a difference.

Dim n As Long, lastrow As Long
lastrow = Range("A65536").End(xlUp).Row
For n = lastrow To 1 Step -1
If Cells(n, 1) <> Range("B1") Then Cells(n, 1).EntireRow.Delete
Next n

Do I need to specify that B1 will always be the cell to reference (ie $B$1). Edit: Just had an epiphany, I also need it to stop after Row 3. I have some headers and such that I don't want removed.

View Replies!   View Related
Delete Row Based On Cell Value
I have the attached spreadsheet set up using a vlookup funtion in D1. I need the macro to be able to find the actual row this number resides in and delete that row whenever E1 is equal to Delete.

View Replies!   View Related
Delete Sheets In Active Workbook
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 Replies!   View Related
ACtiveCell.row: Active Row Is Empty
I am trying to write a statement that sees if the the row below the active row is empty.

I have written the following but it is wrong. Please can someone correct it for me? Thanks!

If IsEmpty(ActiveCell.Row.Offset(1, 0)) = False Then
MsgBox "Not Empty"
End If


View Replies!   View Related
Delete The Whole Row If Any Cell In A Certain Column Has Certain Text
Note: The "Bread Type" column will ALWAYS be in row 1, BUT will ALWAYS be in a different column letter, so I would like this macro to find the "Bread Type" column by name rather than by column number. All criteria should NOT be case sensitive.

In the example below, rows 2, 4 & 5, have the text "Wheat" or "Rye" in the "Bread Type" column, therefore, all 3 of these entire rows should be deleted. Yes, I do mean the ENTIRE row, wipe these rows off the face of the earth

View Replies!   View Related
First Character In Cell Is Text Then Delete Row
In a column I have cells where the first character is numeric and the rest text and also cells where the first character is text.

I want to delete the rows where the first character is text.

View Replies!   View Related
Delete Row If Blank Cell In Column
way to find blank cells in a column(s) and delete the rows corresponding to the cell number. I found this code in one of the previous threads on ozgrid but it just doesnt work for me because it is slowing me down. Please help (I am working on around 60000 rows in Excel 2007)

Sub DeleteBlankARows()
With Application
.Calculation = xlCalculationManual
. ScreenUpdating = False
Dim r As Long
For r = Cells(Rows.Count, 11).End(xlUp).Row To 1 Step -1
If Cells(r, 11) = "" Then Rows(r).Delete
Next r
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

a way to do the reverse viz. for a particular column, I'd like to delete the row corresponding to the non-blank cell in the column.

View Replies!   View Related
How To Delete The Record (row) If Cell = Xyz
I have an Excel worksheet full of data. I would like to search in a specific column for cell that contain "xyz". If found cell = xyz, then I want to delete the whole record or row.

Is there a formula or option to do this?

I am currently looking at each record and deleting it manually. With a worksheet of more than 2000 records, this become a huge task!

View Replies!   View Related
Delete Columns Where Any Cell In Row X Is Empty
If a cell in a range is empty, the column should be deleted. However this doesn't work, as the first column is deleted, but the next cell/column to the right isn't detected/deleted. There can be up to 9 empty rows next to each other.

Here's an example of the code...

Sub x()
For Each OutputSheet In Worksheets
If OutputSheet.Visible = True Then
OutputSheet.Select
Range("IV16").Select
Selection.End(xlToLeft).Select

View Replies!   View Related
Delete Entire Row If Cell Equals
I have a row range 5:20004. In that row range column R may have 'QLD' in the cell.

I am trying to delete all rows within that range that have QLD in column R or delete the row if R <> 'NSW' (would be a better way just in case there is something other than QLD)

to do this on opening the file automatically.

View Replies!   View Related
Delete A Whole Row If Text Appears In A Cell
I have a long spreadsheet and want to remove rows of information from it if cells contain a specific word. If the cell in column A contains the word "district" or "borough" anywhere in the string of text and irrespective of the case I need to delete the whole row.

View Replies!   View Related
Delete Row If Cell Contains Specific Data
I need a quick macro that will search column D for the words "balance forward" then delete the entire row. The macro needs to loop through many rows and it contain more than one "balance forward" reference.

View Replies!   View Related
Macro To Delete Row If Cell Is Empty
I have a worksheet of approx 13000 rows and want to use a macro to delete a row if a certain cell is empty - is this possible?

View Replies!   View Related
If Cell Contains The Word Delete Entire Row
Looking to write a macro to delete an entire row if the word GROWTH is found in any cell under Column C.

View Replies!   View Related
Delete Row Based On First 4 Characters Of Cell
I need a way to check to see if the first four characters of cell A1 is = 2006. If it is, do not delete the row, else, delete the row. Have tried everything I can think of.

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved