Macro to delete upper & lower row of selec...

December 12, 2010 at 09:30:46
Specs: Windows 7
Hi,

I need a macro that can delete 4 upper and 3 lower rows of a selected cell. Here is what I exactly want. I need to find a word (eg:- max) in column A1, When it is found, I need to delete THAT row, above 4 rows and below 3 rows.

Eg:- Search 'max' in column A1, if max is found in column A15, then delete A11 to A18 ( Four upper rows of the word found = A11 to A14, THAT row = A15, three rows below = A16 to A18. I hope you understand my question.


Thanks.


See More: Macro to delete upper & lower row of selec...

Report •


#1
December 12, 2010 at 16:59:41
I understand most of your question except that you keep referring to rows and columns by a single cell reference:

column A1
column A15
row = A15
Four upper rows of the word found = A11 to A14
three rows below = A16 to A18

A is Column
15 is a Row
A15 is a single cell

I can't tell if you want to delete individual cells in Column A or entire rows based on what row the search string is found in.

Please clarify.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#2
December 12, 2010 at 23:03:04
Sorry for the wrong terms I used while referring Row and Columns,

Eg:- Search 'max' in column A1, if 'max' is found in cell A15, then delete 11th row to 18th row ( Four upper rows of the word found = R11 to R14, THAT row = R15, three rows below = R16 to R18. I hope you understand my question.


Report •

#3
December 13, 2010 at 04:55:08
re: "Sorry for the wrong terms I used while referring Row and Columns"

And yet you continue to do it:

"Four upper rows of the word found = R11 to R14, THAT row = R15, three rows below = R16 to R18."

R11, R14, etc. are all specific cell references not Row references.

If you want to refer to a specific Row or a specific Column, use the word Row or Column and then there will be no confusion.

Row 11, Row 14, Column A, etc. Only when you want to refer to a single cell or a range of cells should you use designations such as R14 or R11:R14.

That said, how are you searching for the word e.g. max? Is the user inputting the word in a cell or is there a list of words to be search for or something else?

Is this a one time search for a single word (or list of words) or something that will be done on a regular basis?

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

Related Solutions

#4
December 13, 2010 at 06:00:42
Hi Derby,

Well, I have a spreadsheet where there are some rows I don't need to keep, and I can take 'max' as the search key since its the common word I that contain in the row I want to delete (not only that row but 4 above and 3 below rows also to be deleted.) There are many 'max' we can find in that sheet, all that 'max' row and 4 above and 3 below to be deleted.

Where there is 'max', delete 4 above, 3 below rows and the row that has max. total 8 rows.

Hope my question is clear. Thanks for your help.


Report •

#5
December 13, 2010 at 07:40:13
re: "I can take 'max' as the search key since its the common word I that contain in the row I want to delete "

Once again, we have a "row" vs. "cell" issue. You say that "max" is in the row that you want to delete, but you don't say where in that row. Column A? Column B? Column C? etc.

The following code assumes that "max" can be found in Column A. You can modify the code if Column A is not correct.

I suggest that you try the following code in a back up copy of your workbook since macros can not be undone.

Note: The code starts checking for "max" in A5. If max appears anywhere in A1:A4, you wouldn't have 4 rows above it to delete, so I assume there won't be a "max" above A5.

Option Explicit
Sub NoMax()
Dim numMax As Integer
Dim m As Range
maxCount:
'Count number of "max" in Column A
 numMax = WorksheetFunction.CountIf(Sheets(1).Range("A5:A" & Rows.Count), "max")
'If there's a "max" left the find it and delete rows
  If numMax > 0 Then
    With Sheets(1).Range("A5:A" & Rows.Count)
      Set m = .Find("max")
       If Not m Is Nothing Then
        Rows(m.Row - 4 & ":" & m.Row + 3).Delete
       End If
    End With
'Go back and check to see of there are any more "max"
GoTo maxCount
  End If
End Sub

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#6
December 14, 2010 at 01:24:06
Hi Derby,

Instead of 4 upper and 3 lower rows, can I ask you if you could please change the code as follows…

As we said we would search for word ‘max’ in column C then we delete all adjacent blank row except top most blank row (This is the case for upper rows) then down, we delete all adjacent blank rows.


Suppose…

‘max’ is found in Cell C44 (My search is based on Column C) Then, check how many adjacent blank cells are there upward and downward. (In this example, C38 to C47 are blank cells except C44 as max is in C44). Now, I need to delete all rows from C39 to C47, being C38 the topmost blank cell row 38 is left as it is.

There are many max in column C, Wherever 'max' is there I want this steps to repeat.

Hope my question is clear.


Report •

#7
December 14, 2010 at 05:27:05
re: "Instead of 4 upper and 3 lower rows, can I ask you if you could please change the code as follows…"

No.

We went back and forth making sure I understood your requirements. You used specific examples to to denote row numbers, columns, etc. e.g.:

Search 'max' in column A1, if 'max' is found in cell A15, then delete 11th row to 18th row

I provide the code to do exactly what you asked for and then you change all of the requirements and expect me to redo my work. Sorry, that's not going to happen.

Let's pretend you are a Project Manager for a major corporation and you gave your vendors your requirements and then sent them off to devise a solution. How much more do you think they would charge you to re-do the solution after you told them "Thanks, now change this, and this and this."?

It should be no different when you are getting free help. We have to set up spreadsheets, write code, test it, etc. We don't save every project we work on and when you come back with different requirements we have to set up everything up again, test it all and re-do a lot of work.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#8
December 14, 2010 at 06:25:39
Well, thanks for the help you offered me.

First of all, I wanted to make my question as short as possible.

Now I came across another scenario. If we could change code the way I finally said than we can confront any scenarios that may arise.

Secondly, I didn't know that any help I get from this site is like the way you explained (Let's pretend you are a Project Manager for a major corporation and you gave your vendors half of your...). I actually thought that its some volunteers helping people.

When I found your code worked good, I thought I will ask your for some more help about some codes you provided me.

I now think I can't.


Anyway, Thanks for your time and help.


Report •

#9
December 14, 2010 at 07:42:34
re: "I actually thought that its some volunteers helping people."

That's exactly what it is. However, that doesn't mean we have unlimited time available. We have real jobs and real families and we do this as time allows and we try to help as many people as possible.

Just because the help is free, it doesn't mean that we can (or want) to spend time solving an issue only to have the poster change all of the requirements as soon as the solution was offered.

Here's the issue:

You originally said "Search 'max' in column A1" but as soon as I provided code to meet that requirement you responded "My search is based on Column C". So what's going on? Was your originally requirement wrong or did your spreadsheet change?

You also asked that a specific number of rows above and below be deleted. Now you're asking for a variable number of rows based on whether they are blank or not. Again, I have to ask: Was your originally requirement wrong or did your spreadsheet change?

We can only work with what we're told and it gets frustrating when the requirments are listed incorrectly or change after time and effort has been spent finding a solution to the original requirements.


When you asked that "4 lines above and 3 lines below" be deleted, did the new requirement of "b

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#10
December 14, 2010 at 08:24:28
Well, Sorry for any inconvenience caused. I now understand that you are really bothered. Anyway, from the last message you post I think you want to answer my question.Please don't answer my question until I get back to you with the details. I will give you more detail after some time as I now have to attend something very important. I said, "don't answer now" because I don't want problems again.

Thanks.


Report •


Ask Question