Today, in Episode #1332, Mike "Excel Is Fun" Girvin and Bill "MrExcel" Jelen Filter an Excel Database to clear all 'N' Records, retaining all other data.
Transcript of the video:
Hey! Welcome back, it's another dueling Excel podcast.
I'm Bill Jelen for MrExcel, we'll be joined by Mike Girvin from Excel Is Fun.
I love this question.
Person gets a database like this every single day.
They need to go through and get rid of all of the N records.
Three lines of VBA code, I want to send a shout out, to my friends over in England, John Von der Heyden and Richard Scholar.
I used to have a different way of doing this, they showed me an amazing way.
I will switch over to VBA.
[ Alt F11 ] and it is really comes down to just three lines of code right here, is the first line of code says, hey!
Go to the headings up in row 1 and do an auto filter, go to field 7, that's column G and look for everything that's not and the beautiful thing about auto filter is it will grow to how many records we have, today.
So, I don't have to figure that out.
Go to cell A1 and do current region, in other words [ control Astros ], to select the whole range.
But I don't want to delete the headings.
So, go down one row offset one is saying, go down one row.
Don't have to put the number of columns over and then we're going to use special cells.
That's like using edit, go to special visible cells only and for those visible cells, delete the entire row that you see and then this last line of code says, hey!
Let's turn off the auto filter.
I'm going to run this one line at a time, just because it is so cool.
So, right here we're about to do the auto filter.
Let's take a look at our data set, see the auto filters are not on.
I run that line of code ,I press [ F8 ], bam!
Autofilter is on and we're seeing only the ends next line of code take everything in the current region.
Go down one row to make sure we don't get the headings and delete everything you can see.
Bam! It looks like everything's gone, and then finally the last line of code turn off the auto filter.
Everything comes back, and we're left with everything, but the n records.
VBA, it's the way to go.
Mike, let's see what you have.
Mike: Thanks, MrExcel.
Wow! That is some beautiful code, but since I don't know, how to do code.
I'm going to go ahead and do this with filter.
So, data set comes in to any workbook you have, click in any cell in the data set and I'm going to use the keyboard shortcut, [ CTRL shift L ].
Otherwise you go to data filter, let me get [ control shift L ], then I'm going to come over here and select the drop-down, unselect all and then click [ Blanks ], click [ OK ].
With that cells still selected up there, I'm going to [ control asterisks ], which highlights the whole table and [ control C ], what's great about filter is that control C, automatically copies only the visible cells.
Now, I'm going to insert a new sheet, maybe I already have a sheet right here.
I could use the keyboard shortcut if I didn't have a sheet.
[ Shift F11 ] and then [ ctrl V ] and then I could maybe click up here, to select all the columns and double-click a single time to best fit everything.
So, with a few clicks, filter, turn the filter on, filter it, copy, insert a new sheet and paste, there you have it.
All right! I'll throw it back to MrExcel.
MrExcel: Mike, I love that one [ control shift L ].
I've never used that before.
It's a great one. Hey, it's time, Mike for you to start to learn some code.
I mean, right you know all these amazing Excel tricks if we added a code on top of that, you would be incredibly dangerous.
I'm going to be out there at the end of February, may record them live dueling Excel podcast.
I can start to try and twist your arm to start and learn some code.
Alright! Well, hey! I thank everyone for stopping by, for another dueling Excel podcast.
I'm Bill Jelen from MrExcel.
Thanks to Mike Girvin from Excel Is Fun, see you next week.
I'm Bill Jelen for MrExcel, we'll be joined by Mike Girvin from Excel Is Fun.
I love this question.
Person gets a database like this every single day.
They need to go through and get rid of all of the N records.
Three lines of VBA code, I want to send a shout out, to my friends over in England, John Von der Heyden and Richard Scholar.
I used to have a different way of doing this, they showed me an amazing way.
I will switch over to VBA.
[ Alt F11 ] and it is really comes down to just three lines of code right here, is the first line of code says, hey!
Go to the headings up in row 1 and do an auto filter, go to field 7, that's column G and look for everything that's not and the beautiful thing about auto filter is it will grow to how many records we have, today.
So, I don't have to figure that out.
Go to cell A1 and do current region, in other words [ control Astros ], to select the whole range.
But I don't want to delete the headings.
So, go down one row offset one is saying, go down one row.
Don't have to put the number of columns over and then we're going to use special cells.
That's like using edit, go to special visible cells only and for those visible cells, delete the entire row that you see and then this last line of code says, hey!
Let's turn off the auto filter.
I'm going to run this one line at a time, just because it is so cool.
So, right here we're about to do the auto filter.
Let's take a look at our data set, see the auto filters are not on.
I run that line of code ,I press [ F8 ], bam!
Autofilter is on and we're seeing only the ends next line of code take everything in the current region.
Go down one row to make sure we don't get the headings and delete everything you can see.
Bam! It looks like everything's gone, and then finally the last line of code turn off the auto filter.
Everything comes back, and we're left with everything, but the n records.
VBA, it's the way to go.
Mike, let's see what you have.
Mike: Thanks, MrExcel.
Wow! That is some beautiful code, but since I don't know, how to do code.
I'm going to go ahead and do this with filter.
So, data set comes in to any workbook you have, click in any cell in the data set and I'm going to use the keyboard shortcut, [ CTRL shift L ].
Otherwise you go to data filter, let me get [ control shift L ], then I'm going to come over here and select the drop-down, unselect all and then click [ Blanks ], click [ OK ].
With that cells still selected up there, I'm going to [ control asterisks ], which highlights the whole table and [ control C ], what's great about filter is that control C, automatically copies only the visible cells.
Now, I'm going to insert a new sheet, maybe I already have a sheet right here.
I could use the keyboard shortcut if I didn't have a sheet.
[ Shift F11 ] and then [ ctrl V ] and then I could maybe click up here, to select all the columns and double-click a single time to best fit everything.
So, with a few clicks, filter, turn the filter on, filter it, copy, insert a new sheet and paste, there you have it.
All right! I'll throw it back to MrExcel.
MrExcel: Mike, I love that one [ control shift L ].
I've never used that before.
It's a great one. Hey, it's time, Mike for you to start to learn some code.
I mean, right you know all these amazing Excel tricks if we added a code on top of that, you would be incredibly dangerous.
I'm going to be out there at the end of February, may record them live dueling Excel podcast.
I can start to try and twist your arm to start and learn some code.
Alright! Well, hey! I thank everyone for stopping by, for another dueling Excel podcast.
I'm Bill Jelen from MrExcel.
Thanks to Mike Girvin from Excel Is Fun, see you next week.
