removing rows between certain cells containing text - excel
2
I'm totally new to any kind of programming but I'm trying to remove all rows between two cells containing certain text, then repeat it this through all (~130k rows) in the spreadsheet.
Example
I want to remove all rows between cell B2 and B7, based on the text in those cells
Is there a way a non-programmer might be able to do this? :)
vba excel-2010
edited Nov 25 '18 at 11:10
Navarasu
2,0101822
asked Nov 24 '18 at 22:35
benny4everbenny4ever
111
add a comment |
2
I'm totally new to any kind of programming but I'm trying to remove all rows between two cells containing certain text, then repeat it this through all (~130k rows) in the spreadsheet.
Example
I want to remove all rows between cell B2 and B7, based on the text in those cells
Is there a way a non-programmer might be able to do this? :)
vba excel-2010
edited Nov 25 '18 at 11:10
Navarasu
2,0101822
asked Nov 24 '18 at 22:35
benny4everbenny4ever
111
Welcome to SO...if i understood your question do you want remove all rows between cells Standard run->Setup run included?
– Ferdinando
Nov 25 '18 at 7:48
add a comment |
2
2
2
I'm totally new to any kind of programming but I'm trying to remove all rows between two cells containing certain text, then repeat it this through all (~130k rows) in the spreadsheet.
Example
I want to remove all rows between cell B2 and B7, based on the text in those cells
Is there a way a non-programmer might be able to do this? :)
vba excel-2010
edited Nov 25 '18 at 11:10
Navarasu
2,0101822
asked Nov 24 '18 at 22:35
benny4everbenny4ever
111
I'm totally new to any kind of programming but I'm trying to remove all rows between two cells containing certain text, then repeat it this through all (~130k rows) in the spreadsheet.
Example
I want to remove all rows between cell B2 and B7, based on the text in those cells
Is there a way a non-programmer might be able to do this? :)
vba excel-2010
vba excel-2010
edited Nov 25 '18 at 11:10
Navarasu
2,0101822
asked Nov 24 '18 at 22:35
benny4everbenny4ever
111
edited Nov 25 '18 at 11:10
Navarasu
2,0101822
asked Nov 24 '18 at 22:35
benny4everbenny4ever
111
edited Nov 25 '18 at 11:10
Navarasu
2,0101822
edited Nov 25 '18 at 11:10
Navarasu
2,0101822
edited Nov 25 '18 at 11:10
Navarasu
2,0101822
2,0101822
asked Nov 24 '18 at 22:35
benny4everbenny4ever
111
asked Nov 24 '18 at 22:35
benny4everbenny4ever
111
asked Nov 24 '18 at 22:35
benny4everbenny4ever
111
111
Welcome to SO...if i understood your question do you want remove all rows between cells Standard run->Setup run included?
– Ferdinando
Nov 25 '18 at 7:48
add a comment |
Welcome to SO...if i understood your question do you want remove all rows between cells Standard run->Setup run included?
– Ferdinando
Nov 25 '18 at 7:48
Welcome to SO...if i understood your question do you want remove all rows between cells Standard run->Setup run included?
– Ferdinando
Nov 25 '18 at 7:48
Welcome to SO...if i understood your question do you want remove all rows between cells Standard run->Setup run included?
– Ferdinando
Nov 25 '18 at 7:48
add a comment |
2 Answers
2
active
oldest
votes
0
In this example i suppose that in Column B there aren't two "consecutive Standard run->Setup run" and viceversa.
initial sheet:
final sheet
This is the code:
Sub test()
'remove cells between two cells Standard run->Setup run and Setup run->Standard run
Dim count, i, numCols As Long
Dim startRemove, endRemove, startText, endText As String
Dim rng As Range
startText = "Standard run->Setup run" 'text where we begin to remove the cells
endText = "Setup run->Standard run" ' text where we finish to remove the cells
startRemove = "" 'in this variable we'll put the cell address of Standard run->Setup run Cells(2, 7).Address
endRemove = "" 'in this variable we'll put the cell address of Setup run->Standard run Cells(7, 7).Address
'-----control column B
'count how many rows
numCols = Range("B:B").Cells.SpecialCells(xlCellTypeConstants).count
For i = 1 To numCols
If Cells(i, 2) = startText Then ' control if current cell has Standard run->Setup run
startRemove = Cells(i, 2).Address
ElseIf Cells(i, 2) = endText Then ' control if current cell has Setup run->Standard run
endRemove = Cells(i, 2).Address
Range(startRemove & ":" & endRemove).Delete
startRemove = ""
endRemove = ""
End If
Next i
Range("B1:B" & numCols).Cells.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp ' delete empty rows created
End Sub
Hope this helps.
answered Nov 25 '18 at 8:47
FerdinandoFerdinando
5891417
Wow, thanks for a great answer! I cant really get it to work just yet. When I run it it responds no cells could be found so I guess I'm doing something wrong here (Run-time error '1004': No cells were found.) The cell adress of standard run -> setup run is C21 so I put (21,3) in the reference, is this correct way to do it? And then the same procedure for Setup run -> standard run. Like this: startRemove = "(21, 3)" endRemove = "(26, 3)"
– benny4ever
Nov 25 '18 at 17:56
Controllo the text cella...i wrote Standard run->Set-up run without the space...control in tour cell how it is write...and after copy and paste into variabile startText and endText...keep me update...
– Ferdinando
Nov 25 '18 at 18:22
Into startRemove there is the address example C21
– Ferdinando
Nov 25 '18 at 18:24
@benny4ever but do you have standard run->Setup run in column C?...
– Ferdinando
Nov 25 '18 at 18:54
add a comment |
0
Here is solution that does not require VBA
I have inserted a formula in column C, you can apply a filter to remove the rows.
The formula in cell C2 is
=IF(B2="Standard run->Setup run","start",IF(B2="Setup run->Standard run","end",IF(C1="start","remove",IF(C1="end","do not remove",C1))))
answered Nov 26 '18 at 8:20
usmanhaqusmanhaq
1,113128
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53462942%2fremoving-rows-between-certain-cells-containing-text-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
In this example i suppose that in Column B there aren't two "consecutive Standard run->Setup run" and viceversa.
initial sheet:
final sheet
This is the code:
Sub test()
'remove cells between two cells Standard run->Setup run and Setup run->Standard run
Dim count, i, numCols As Long
Dim startRemove, endRemove, startText, endText As String
Dim rng As Range
startText = "Standard run->Setup run" 'text where we begin to remove the cells
endText = "Setup run->Standard run" ' text where we finish to remove the cells
startRemove = "" 'in this variable we'll put the cell address of Standard run->Setup run Cells(2, 7).Address
endRemove = "" 'in this variable we'll put the cell address of Setup run->Standard run Cells(7, 7).Address
'-----control column B
'count how many rows
numCols = Range("B:B").Cells.SpecialCells(xlCellTypeConstants).count
For i = 1 To numCols
If Cells(i, 2) = startText Then ' control if current cell has Standard run->Setup run
startRemove = Cells(i, 2).Address
ElseIf Cells(i, 2) = endText Then ' control if current cell has Setup run->Standard run
endRemove = Cells(i, 2).Address
Range(startRemove & ":" & endRemove).Delete
startRemove = ""
endRemove = ""
End If
Next i
Range("B1:B" & numCols).Cells.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp ' delete empty rows created
End Sub
Hope this helps.
answered Nov 25 '18 at 8:47
FerdinandoFerdinando
5891417
Wow, thanks for a great answer! I cant really get it to work just yet. When I run it it responds no cells could be found so I guess I'm doing something wrong here (Run-time error '1004': No cells were found.) The cell adress of standard run -> setup run is C21 so I put (21,3) in the reference, is this correct way to do it? And then the same procedure for Setup run -> standard run. Like this: startRemove = "(21, 3)" endRemove = "(26, 3)"
– benny4ever
Nov 25 '18 at 17:56
Controllo the text cella...i wrote Standard run->Set-up run without the space...control in tour cell how it is write...and after copy and paste into variabile startText and endText...keep me update...
– Ferdinando
Nov 25 '18 at 18:22
Into startRemove there is the address example C21
– Ferdinando
Nov 25 '18 at 18:24
@benny4ever but do you have standard run->Setup run in column C?...
– Ferdinando
Nov 25 '18 at 18:54
add a comment |
0
In this example i suppose that in Column B there aren't two "consecutive Standard run->Setup run" and viceversa.
initial sheet:
final sheet
This is the code:
Sub test()
'remove cells between two cells Standard run->Setup run and Setup run->Standard run
Dim count, i, numCols As Long
Dim startRemove, endRemove, startText, endText As String
Dim rng As Range
startText = "Standard run->Setup run" 'text where we begin to remove the cells
endText = "Setup run->Standard run" ' text where we finish to remove the cells
startRemove = "" 'in this variable we'll put the cell address of Standard run->Setup run Cells(2, 7).Address
endRemove = "" 'in this variable we'll put the cell address of Setup run->Standard run Cells(7, 7).Address
'-----control column B
'count how many rows
numCols = Range("B:B").Cells.SpecialCells(xlCellTypeConstants).count
For i = 1 To numCols
If Cells(i, 2) = startText Then ' control if current cell has Standard run->Setup run
startRemove = Cells(i, 2).Address
ElseIf Cells(i, 2) = endText Then ' control if current cell has Setup run->Standard run
endRemove = Cells(i, 2).Address
Range(startRemove & ":" & endRemove).Delete
startRemove = ""
endRemove = ""
End If
Next i
Range("B1:B" & numCols).Cells.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp ' delete empty rows created
End Sub
Hope this helps.
answered Nov 25 '18 at 8:47
FerdinandoFerdinando
5891417
Wow, thanks for a great answer! I cant really get it to work just yet. When I run it it responds no cells could be found so I guess I'm doing something wrong here (Run-time error '1004': No cells were found.) The cell adress of standard run -> setup run is C21 so I put (21,3) in the reference, is this correct way to do it? And then the same procedure for Setup run -> standard run. Like this: startRemove = "(21, 3)" endRemove = "(26, 3)"
– benny4ever
Nov 25 '18 at 17:56
Controllo the text cella...i wrote Standard run->Set-up run without the space...control in tour cell how it is write...and after copy and paste into variabile startText and endText...keep me update...
– Ferdinando
Nov 25 '18 at 18:22
Into startRemove there is the address example C21
– Ferdinando
Nov 25 '18 at 18:24
@benny4ever but do you have standard run->Setup run in column C?...
– Ferdinando
Nov 25 '18 at 18:54
add a comment |
0
0
0
In this example i suppose that in Column B there aren't two "consecutive Standard run->Setup run" and viceversa.
initial sheet:
final sheet
This is the code:
Sub test()
'remove cells between two cells Standard run->Setup run and Setup run->Standard run
Dim count, i, numCols As Long
Dim startRemove, endRemove, startText, endText As String
Dim rng As Range
startText = "Standard run->Setup run" 'text where we begin to remove the cells
endText = "Setup run->Standard run" ' text where we finish to remove the cells
startRemove = "" 'in this variable we'll put the cell address of Standard run->Setup run Cells(2, 7).Address
endRemove = "" 'in this variable we'll put the cell address of Setup run->Standard run Cells(7, 7).Address
'-----control column B
'count how many rows
numCols = Range("B:B").Cells.SpecialCells(xlCellTypeConstants).count
For i = 1 To numCols
If Cells(i, 2) = startText Then ' control if current cell has Standard run->Setup run
startRemove = Cells(i, 2).Address
ElseIf Cells(i, 2) = endText Then ' control if current cell has Setup run->Standard run
endRemove = Cells(i, 2).Address
Range(startRemove & ":" & endRemove).Delete
startRemove = ""
endRemove = ""
End If
Next i
Range("B1:B" & numCols).Cells.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp ' delete empty rows created
End Sub
Hope this helps.
answered Nov 25 '18 at 8:47
FerdinandoFerdinando
5891417
In this example i suppose that in Column B there aren't two "consecutive Standard run->Setup run" and viceversa.
initial sheet:
final sheet
This is the code:
Sub test()
'remove cells between two cells Standard run->Setup run and Setup run->Standard run
Dim count, i, numCols As Long
Dim startRemove, endRemove, startText, endText As String
Dim rng As Range
startText = "Standard run->Setup run" 'text where we begin to remove the cells
endText = "Setup run->Standard run" ' text where we finish to remove the cells
startRemove = "" 'in this variable we'll put the cell address of Standard run->Setup run Cells(2, 7).Address
endRemove = "" 'in this variable we'll put the cell address of Setup run->Standard run Cells(7, 7).Address
'-----control column B
'count how many rows
numCols = Range("B:B").Cells.SpecialCells(xlCellTypeConstants).count
For i = 1 To numCols
If Cells(i, 2) = startText Then ' control if current cell has Standard run->Setup run
startRemove = Cells(i, 2).Address
ElseIf Cells(i, 2) = endText Then ' control if current cell has Setup run->Standard run
endRemove = Cells(i, 2).Address
Range(startRemove & ":" & endRemove).Delete
startRemove = ""
endRemove = ""
End If
Next i
Range("B1:B" & numCols).Cells.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp ' delete empty rows created
End Sub
Hope this helps.
answered Nov 25 '18 at 8:47
FerdinandoFerdinando
5891417
answered Nov 25 '18 at 8:47
FerdinandoFerdinando
5891417
answered Nov 25 '18 at 8:47
FerdinandoFerdinando
5891417
answered Nov 25 '18 at 8:47
FerdinandoFerdinando
5891417
5891417
Wow, thanks for a great answer! I cant really get it to work just yet. When I run it it responds no cells could be found so I guess I'm doing something wrong here (Run-time error '1004': No cells were found.) The cell adress of standard run -> setup run is C21 so I put (21,3) in the reference, is this correct way to do it? And then the same procedure for Setup run -> standard run. Like this: startRemove = "(21, 3)" endRemove = "(26, 3)"
– benny4ever
Nov 25 '18 at 17:56
Controllo the text cella...i wrote Standard run->Set-up run without the space...control in tour cell how it is write...and after copy and paste into variabile startText and endText...keep me update...
– Ferdinando
Nov 25 '18 at 18:22
Into startRemove there is the address example C21
– Ferdinando
Nov 25 '18 at 18:24
@benny4ever but do you have standard run->Setup run in column C?...
– Ferdinando
Nov 25 '18 at 18:54
add a comment |
Wow, thanks for a great answer! I cant really get it to work just yet. When I run it it responds no cells could be found so I guess I'm doing something wrong here (Run-time error '1004': No cells were found.) The cell adress of standard run -> setup run is C21 so I put (21,3) in the reference, is this correct way to do it? And then the same procedure for Setup run -> standard run. Like this: startRemove = "(21, 3)" endRemove = "(26, 3)"
– benny4ever
Nov 25 '18 at 17:56
Controllo the text cella...i wrote Standard run->Set-up run without the space...control in tour cell how it is write...and after copy and paste into variabile startText and endText...keep me update...
– Ferdinando
Nov 25 '18 at 18:22
Into startRemove there is the address example C21
– Ferdinando
Nov 25 '18 at 18:24
@benny4ever but do you have standard run->Setup run in column C?...
– Ferdinando
Nov 25 '18 at 18:54
Wow, thanks for a great answer! I cant really get it to work just yet. When I run it it responds no cells could be found so I guess I'm doing something wrong here (Run-time error '1004': No cells were found.) The cell adress of standard run -> setup run is C21 so I put (21,3) in the reference, is this correct way to do it? And then the same procedure for Setup run -> standard run. Like this: startRemove = "(21, 3)" endRemove = "(26, 3)"
– benny4ever
Nov 25 '18 at 17:56
Wow, thanks for a great answer! I cant really get it to work just yet. When I run it it responds no cells could be found so I guess I'm doing something wrong here (Run-time error '1004': No cells were found.) The cell adress of standard run -> setup run is C21 so I put (21,3) in the reference, is this correct way to do it? And then the same procedure for Setup run -> standard run. Like this: startRemove = "(21, 3)" endRemove = "(26, 3)"
– benny4ever
Nov 25 '18 at 17:56
Controllo the text cella...i wrote Standard run->Set-up run without the space...control in tour cell how it is write...and after copy and paste into variabile startText and endText...keep me update...
– Ferdinando
Nov 25 '18 at 18:22
Controllo the text cella...i wrote Standard run->Set-up run without the space...control in tour cell how it is write...and after copy and paste into variabile startText and endText...keep me update...
– Ferdinando
Nov 25 '18 at 18:22
Into startRemove there is the address example C21
– Ferdinando
Nov 25 '18 at 18:24
Into startRemove there is the address example C21
– Ferdinando
Nov 25 '18 at 18:24
@benny4ever but do you have standard run->Setup run in column C?...
– Ferdinando
Nov 25 '18 at 18:54
@benny4ever but do you have standard run->Setup run in column C?...
– Ferdinando
Nov 25 '18 at 18:54
add a comment |
0
Here is solution that does not require VBA
I have inserted a formula in column C, you can apply a filter to remove the rows.
The formula in cell C2 is
=IF(B2="Standard run->Setup run","start",IF(B2="Setup run->Standard run","end",IF(C1="start","remove",IF(C1="end","do not remove",C1))))
answered Nov 26 '18 at 8:20
usmanhaqusmanhaq
1,113128
add a comment |
0
Here is solution that does not require VBA
I have inserted a formula in column C, you can apply a filter to remove the rows.
The formula in cell C2 is
=IF(B2="Standard run->Setup run","start",IF(B2="Setup run->Standard run","end",IF(C1="start","remove",IF(C1="end","do not remove",C1))))
answered Nov 26 '18 at 8:20
usmanhaqusmanhaq
1,113128
add a comment |
0
0
0
Here is solution that does not require VBA
I have inserted a formula in column C, you can apply a filter to remove the rows.
The formula in cell C2 is
=IF(B2="Standard run->Setup run","start",IF(B2="Setup run->Standard run","end",IF(C1="start","remove",IF(C1="end","do not remove",C1))))
answered Nov 26 '18 at 8:20
usmanhaqusmanhaq
1,113128
Here is solution that does not require VBA
I have inserted a formula in column C, you can apply a filter to remove the rows.
The formula in cell C2 is
=IF(B2="Standard run->Setup run","start",IF(B2="Setup run->Standard run","end",IF(C1="start","remove",IF(C1="end","do not remove",C1))))
answered Nov 26 '18 at 8:20
usmanhaqusmanhaq
1,113128
answered Nov 26 '18 at 8:20
usmanhaqusmanhaq
1,113128
answered Nov 26 '18 at 8:20
usmanhaqusmanhaq
1,113128
answered Nov 26 '18 at 8:20
usmanhaqusmanhaq
1,113128
1,113128
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53462942%2fremoving-rows-between-certain-cells-containing-text-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Welcome to SO...if i understood your question do you want remove all rows between cells Standard run->Setup run included?
– Ferdinando
Nov 25 '18 at 7:48