Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

Como Obtener REGISTROS UNICOS de Una Columna FILTRO AVANZADO en Excel VBA #529

Filtro avanzado con macro Filtro registros unicos

Autofiltro Avanzado con Macro – Filtrar Registros Unicos

En este post se muestra como crear un Filtro Avanzado con macro procediendo a filtrar solamente los datos únicos de una columna, es similar a seleccionar en el menú de Excel, Filtro Avanzado, en este caso lo hace a través de una macro-

Se el mejor manejando Excel, hazlo en forma fácil y sencilla, click acá, si quieres aprender sobre Excel en inglés, entonces debes hacer click here. Si lo que necesitas es aprender o profundizar sobre la programación de macros con VBA este es unos de los mejores cursos on line que he visto en internet, te lo recomiendo no te arrepentirás.

  
 

Mira la macro en acción, una explicación más detallada de su codificación y funcionamiento, recomiendo observar para una más fácil comprensión de la macro; suscribe a nuestro canal de You Tube, mira el playlist con  vídeos relacionados donde podrás ver la macros relacionadas en acción con una explicación en forma visual que ayudará a entender el ejemplo en forma más fácil.

FILTRO AVANZADO – Filtrar registros sin duplicados

En este ejemplo utilizando código de VBA se procede a realizar un Filtro Avanzado, extrayendo y mostrando solamente los datos únicos, la macro filtra la celda determinada, establece cuales son los registros únicos y los mismos son mostrados en la hoja de Excel.

El Filtro avanzado creado con macro de Excel VBA, filtra los datos seleccionados, copiando los mismos a la hoja dos, no realiza un filtro en el lugar, sino que copia a otra hoja.

Explicación del código para crear Filtro Avanzado y Extraer Solo Datos Unicos

Para crear el Filtro Avanzado con macros, primero se crea un objeto con la hoja 1 que es donde se mostrarán los datos filtrados y con la hoja 2 que es donde están los datos a filtrar, se utiliza el siguiente código

Set a = Sheets(«Hoja1»)
Set b = Sheets(«Hoja2»)

Se determina la última fila con datos, en la hoja donde se encuentran los datos a filtrar y se crea el objeto «Rng» que contine el rango a filtrar, se usa el siguiente código:

uf = b.Range(«A» & Rows.Count).End(xlUp).Row
Set Rng = b.Range(«D2:D» & uf)

Luego se limpia la hoja y rango donde se copiarán los datos para que no provoque problemas, con el siguiente código:

ufa = a.Range(«A» & Rows.Count).End(xlUp).Row
If ufa = 1 Then ufa = 2
a.Range(«A2:H» & ufa).Clear

Luego se con el siguiente código  aplica un Filtro Avanzado al rango seleccionado, copiando los datos filtrados a otro rango, que en este ejemplo se encuentra en la hoja1, se usa el siguiente código:

Rng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=a.Range(«A2»), Unique:=1

Notese que en el código anterior, que el Filtro Avanzado creado con macro no contiene ningún criterio de filtrado, estableciendo que los datos a filtrar son los datos únicos por eso «Unique» = 1, también se puede escribir «True» en vez de 1.

Descarga el Libro Extraer Registros Unicos con Filtro Avanzado con Macro

Al final del post encontrarás el link para descargar este ejemplo, el mismo es totalmente gratuito y libre su uso, solicito aportar para sostener esta web, si está dentro de tus posibilidades, desde ya muchas gracias.

Código para Crear Filtro Avanzado con Macro de Excel VBA

Sub RegistrosUnicos()
‘*********** by marcrodos **** https://macrosenexcel.com *** https://www.youtube.com/c/programarexcel?sub_confirmation=1 ********
Application.ScreenUpdating = False
Set a = Sheets(«Hoja1»)
Set b = Sheets(«Hoja2»)
uf = b.Range(«A» & Rows.Count).End(xlUp).Row
Set Rng = b.Range(«D2:D» & uf)

ufa = a.Range(«A» & Rows.Count).End(xlUp).Row
If ufa = 1 Then ufa = 2
a.Range(«A2:H» & ufa).Clear

Rng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=a.Range(«A2»), Unique:=1

ufa = a.Range(«A» & Rows.Count).End(xlUp).Row
For x = ufa To 2 Step -1
If a.Cells(x, 1) = Empty Then a.Cells(x, 1).EntireRow.Delete
Next x

MsgBox («Los datos se extrajeron con éxito»), vbInformation, «https://macrosenexcel.com»
Application.ScreenUpdating = False
End Sub

Sub Borra()
Set d = Sheets(«Hoja1»)
uf = d.Range(«A» & Rows.Count).End(xlUp).Row
If uf = 1 Then uf = 2
d.Range(«A2:H» & uf).Clear
End Sub



Si te fue de utilidad puedes INVITARME UN CAFÉ y de esta manera ayudar a seguir manteniendo la página, CLICK para descargar en ejemplo en forma gratuita.

If this post was helpful INVITE ME A COFFEE and so help keep up the page, CLICK to download free example.

Donate:
Cuenta Paypal: https://paypal.me/programarexcel
Cuenta Bitcoin: 1KBGGb8fyDzyR3X1Rie6m7VguzaAfngNbd
Cuenta Ether: 0x41Bbd24556914C83a31217eBb3BC49789b66e407

Summary
Author Rating
1star1star1star1star1star
Aggregate Rating
no rating based on 0 votes

Entradas relacionadas

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Comentarios (75)

I like reading through an article that will make men and women think.
Also, many thanks for allowing me to comment!

Responder

You have made some good points there. I looked on the web to
learn more about the issue and found most people will go along with your views on this site.

Responder

hi!,I like your writing so much! percentage we be in contact more approximately your
article on AOL? I require an expert on this space to unravel my
problem. Maybe that’s you! Looking ahead to see you.

Responder

If you are going for best contents like I do, only visit this web site all the time for the reason that it provides quality contents, thanks

Responder

I think this is one of the most vital information for me. And i’m glad reading your article.
But should remark on some general things, The website style is great,
the articles is really excellent : D. Good job, cheers

Responder

I am truly grateful to the owner of this web page who has shared this enormous article at at
this place.

Responder

Hi, yes this post is genuinely nice and I have learned lot of
things from it concerning blogging. thanks.

Responder

Hey I know this is off topic but I was wondering if you
knew of any widgets I could add to my blog that automatically tweet my newest twitter updates.
I’ve been looking for a plug-in like this for quite
some time and was hoping maybe you would have some experience with something like this.

Please let me know if you run into anything. I truly enjoy reading your blog and I look forward to your new updates.

Responder

I like the helpful information you provide on your
articles. I will bookmark your blog and check again right here regularly.

I am slightly sure I’ll be informed plenty of new stuff right here!
Good luck for the next!

Responder

You really make it seem so easy with your presentation but I find this topic to be really something that I think I would never understand.
It seems too complicated and very broad for me. I am looking forward for
your next post, I will try to get the hang of it!

Responder

It’s a shame you don’t have a donate button! I’d without a doubt donate to
this excellent blog! I guess for now i’ll settle for
book-marking and adding your RSS feed to my Google account.
I look forward to brand new updates and will talk about this website with my Facebook
group. Chat soon!

Responder

This website truly has all of the information I needed concerning this subject and didn’t know who to ask.

Responder

Nice post. I was checking constantly this blog and I am inspired!
Extremely useful info specifically the ultimate part
🙂 I care for such information much. I used to be looking for
this certain information for a long time. Thank you and
best of luck.

Responder

You really make it seem so easy with your
presentation but I find this topic to be really something which I think I would never
understand. It seems too complicated and extremely broad for me.
I’m looking forward for your next post, I will try to
get the hang of it!

Responder

If you are going for most excellent contents like I do,
just pay a visit this web page everyday as it
presents feature contents, thanks

Responder

Remarkable! Its actually awesome piece of writing, I have got much clear idea
on the topic of from this piece of writing.

Responder

When someone writes an paragraph he/she keeps the thought of a
user in his/her mind that how a user can know it.
Thus that’s why this paragraph is amazing.
Thanks!

Responder

I go to see daily some blogs and sites to read articles or reviews, but this blog
provides quality based writing.

Responder

You really make it seem so easy with your presentation but I find this matter to be actually something which I think I would never understand.

It seems too complicated and extremely broad for me.
I am looking forward for your next post, I will try to get
the hang of it!

Responder

What a stuff of un-ambiguity and preserveness of valuable knowledge regarding
unexpected emotions.

Responder

Write more, thats all I have to say. Literally, it seems as
though you relied on the video to make your point.
You clearly know what youre talking about, why throw away
your intelligence on just posting videos to your blog when you could be giving us something enlightening to
read?

Responder

This paragraph will help the internet viewers for setting up new blog
or even a blog from start to end.

Responder

[url=https://cialisexpress.com/]tadalafil canada online[/url] [url=https://cialisgenericnow.com/]generic cialis 50 mg[/url] [url=https://viagraoft.com/]cheap brand viagra 100mg[/url] [url=https://sildenafileasy.com/]sildenafil online coupon[/url] [url=https://bestviagrapills.com/]cheapest generic viagra prices[/url]

Responder

Appreciate the recommendation. Will try it out.

Responder

Hi, just wanted to tell you, I liked this blog post.
It was helpful. Keep on posting!

Responder

Howdy! I could have sworn I’ve been to this website before but after browsing through some
of the post I realized it’s new to me. Nonetheless, I’m definitely glad I found it and I’ll be bookmarking and checking back frequently!

Responder

Someone necessarily assist to make seriously posts I
would state. This is the very first time I frequented your web page and so far?
I surprised with the analysis you made to make this particular post incredible.
Excellent job!

Responder

Hello everyone, it’s my first pay a quick visit at this web
site, and post is in fact fruitful in support of me, keep up posting these types of posts.

Responder

I’m gone to tell my little brother, that he should also pay a visit this webpage
on regular basis to get updated from most up-to-date news.

Responder

bookmarked!!, I like your site!

Responder

What i don’t understood is if truth be told how you are not actually much more
well-favored than you might be now. You are very intelligent.

You realize therefore considerably when it comes to this matter, produced me in my opinion believe it from a lot of numerous angles.
Its like women and men are not interested except it’s something to do with
Woman gaga! Your own stuffs great. At all times care for it
up!

Responder

I am actually grateful to the holder of this
web site who has shared this impressive piece of writing at here.

Responder

Hi there colleagues, fastidious piece of writing and good urging commented at this
place, I am actually enjoying by these.

Responder

I know this if off topic but I’m looking into starting my own weblog and was wondering what all is required to get setup?
I’m assuming having a blog like yours would cost a pretty penny?

I’m not very web smart so I’m not 100% positive.

Any recommendations or advice would be greatly appreciated.
Many thanks

Responder

[url=https://medicinecialis.com/]cialis pharmacy uk[/url] [url=https://fasttadalafil.com/]cialis generic 20 mg[/url] [url=https://viagracpill.com/]viagra for women buy[/url] [url=https://tadalafillowprice.com/]cialis 2mg[/url] [url=https://tadalafilworx.com/]tadalafil tablets india online[/url]

Responder

What’s up to every one, it’s actually a fastidious for me to visit this
web page, it consists of valuable Information.

Responder

[url=http://sildenafilchem.com/]sildenafil 100 mg tablet usa[/url] [url=http://viagrabuycheap.com/]generic viagra 100mg uk[/url]

Responder

[url=https://sildenafileasy.com/]canadian online pharmacy sildenafil[/url] [url=https://onlinemediorder.com/]provigil 100[/url] [url=https://sildenafilchem.com/]where can i buy sildenafil[/url]

Responder

[url=https://sildenafilchem.com/]sildenafil online purchase india[/url]

Responder

[url=https://phdtabs.com/]propecia pharmacy uk[/url] [url=https://tadalafilworx.com/]tadalafil 20mg buy[/url] [url=https://fasttadalafil.com/]tadalafil australia[/url] [url=https://buysildenafilpills.com/]sildenafil cost in india[/url] [url=https://doviagra.com/]viagra online price[/url] [url=https://sildenafilpharm.com/]viagra online cheap[/url] [url=https://unocialis.com/]tadalafil generic over the counter[/url] [url=https://xrpills.com/]accutane order[/url] [url=https://medptr.com/]vardenafil in canada[/url] [url=https://opivermectin.com/]how to buy stromectol[/url]

Responder

We absolutely love your blog and find nearly all of your
post’s to be exactly I’m looking for. can you offer guest writers to write content for yourself?
I wouldn’t mind creating a post or elaborating on some
of the subjects you write about here. Again, awesome weblog!

Responder

I do not even know how I ended up here, but I thought this post
was good. I do not know who you are but certainly you are going to a famous blogger if you are not
already 😉 Cheers!

Responder

Pretty section of content. I simply stumbled upon your weblog and in accession capital to
assert that I acquire in fact enjoyed account your weblog posts.
Anyway I will be subscribing on your feeds and even I achievement
you get entry to persistently rapidly.

Responder

[url=https://medptr.com/]mexico levitra cheap[/url] [url=https://sildenafilchem.com/]sildenafil citrate india[/url]

Responder

Pretty nice post. I just stumbled upon your blog and
wanted to say that I’ve really enjoyed browsing your blog posts.

After all I will be subscribing to your feed and I hope you write again soon!

Responder

[url=https://viagraxbuy.com/]viagra cost prescription[/url]

Responder

I loved as much as you’ll receive carried out right here.
The sketch is attractive, your authored subject matter stylish.
nonetheless, you command get bought an edginess over that you wish be
delivering the following. unwell unquestionably come further formerly again as exactly the same nearly very
often inside case you shield this hike.

Responder

I feel this is one of the most significant
information for me. And i am glad studying your article. But want to observation on few basic things, The site taste
is wonderful, the articles is truly great : D. Just right task,
cheers

Responder

There’s certainly a lot to find out about this subject.
I like all of the points you made.

Responder

[url=https://sildenafilchem.com/]cheap generic sildenafil[/url] [url=https://cheapestsildenafil.com/]cheapest online 100 mg sildenafil[/url] [url=https://sildenafilfp.com/]sildenafil citrate pfizer[/url] [url=https://pharmacyomni.com/]indian pharmacies safe[/url]

Responder

You have made some good points there. I checked on the web
to find out more about the issue and found most people will
go along with your views on this site.

Responder

It’s going to be end of mine day, but before end I am reading this impressive paragraph to improve my knowledge.

Responder

[url=https://rxremi.com/]amoxicillin 500mg pill[/url] [url=https://pharmacyomni.com/]uk pharmacy[/url] [url=https://viagrasm.com/]buying viagra in australia over the counter[/url] [url=https://anycialis.com/]tadalafil 40 mg online[/url] [url=https://ivermectinpn.com/]ivermectin 3 mg tabs[/url]

Responder

Hello it’s me, I am also visiting this web site on a regular basis, this web
page is really nice and the users are truly sharing fastidious thoughts.

Responder

Good post. I will be dealing with many of these issues as well..

Responder

Great beat ! I wish to apprentice at the same time as you amend your site,
how can i subscribe for a weblog website? The account helped me a appropriate deal.
I were a little bit familiar of this your broadcast offered vivid transparent
concept

Responder

I don’t know if it’s just me or if everybody else encountering issues with your site.
It appears as though some of the text within your posts are running off the screen. Can somebody else please comment
and let me know if this is happening to
them too? This might be a issue with my browser because I’ve had this happen previously.
Cheers

Responder

[url=http://ivermectinpn.com/]cost of stromectol[/url] [url=http://vdphealth.com/]lasix 20 mg cost[/url] [url=http://oxlpharm.com/]hydroxychloroquine sulfate tabs 200mg[/url] [url=http://startpills.com/]where to buy lisinopril[/url] [url=http://bestviagrapills.com/]viagra capsules in india[/url] [url=http://opivermectin.com/]stromectol tablets buy online[/url] [url=http://sildenafilmedicine.com/]best price for generic sildenafil[/url] [url=http://aventolin.com/]combivent generic price[/url]

Responder

Hi, everything is going perfectly here and ofcourse every one is sharing
facts, that’s in fact fine, keep up writing.

Responder

We absolutely love your blog and find nearly all of your post’s to be precisely what I’m looking for.
Do you offer guest writers to write content available for you?

I wouldn’t mind publishing a post or elaborating on a number of the
subjects you write in relation to here. Again, awesome site!

Responder

[url=https://viagrahtab.com/]generic viagra sildenafil[/url] [url=https://incrhealth.com/]order antabuse no prescription[/url] [url=https://ivermectinpn.com/]stromectol over the counter[/url] [url=https://ivermectinop.com/]ivermectin pill cost[/url] [url=https://sildenafilchem.com/]generic sildenafil from india[/url] [url=https://crmeds.com/]where to buy prozac uk[/url] [url=https://tadalafilmtab.com/]cialis 20 mg tadalafil[/url] [url=https://dwpills.com/]buy generic lexapro online[/url]

Responder

I am regular visitor, how are you everybody? This piece of writing posted at this web page is really pleasant.

Responder

[url=https://viagrawithnoprescription.com/]generic viagra from canada pharmacy[/url]

Responder

Excellent post. I absolutely love this website.
Stick with it!

Responder

[url=http://cialisfmed.com/]cialis gel online[/url] [url=http://buyrpills.com/]doxycycline 100mg india[/url]

Responder

I visit every day some web sites and sites to read articles, however
this website presents feature based posts.

Responder

[url=https://buyrpills.com/]doxycycline without a prescription[/url] [url=https://viagrabuycheap.com/]viagra professional online[/url] [url=https://pharmacyzeus.com/]best online pharmacy[/url] [url=https://cialissearch.com/]can you buy cialis over the counter in mexico[/url] [url=https://pviagra.com/]online viagra purchase[/url] [url=https://viagraconn.com/]viagra cheap prices[/url] [url=https://sildenafilfp.com/]generic viagra 100mg online[/url] [url=https://bestviagrapills.com/]viagra pills online india[/url] [url=https://xviagrahot.com/]sales viagra[/url] [url=https://viagrabi.com/]where can i buy generic viagra online[/url]

Responder

Hello to every one, the contents present at this site are actually awesome
for people knowledge, well, keep up the good work fellows.

Responder

[url=http://pharmacylewis.com/]my canadian pharmacy rx[/url] [url=http://dwpills.com/]lexapro[/url] [url=http://cialisgenericnow.com/]cialis daily uk[/url] [url=http://pharmacyomni.com/]cheapest pharmacy to fill prescriptions with insurance[/url] [url=http://buyrpills.com/]doxycycline 100 mg pill[/url]

Responder

Today, while I was at work, my sister stole my iPad and tested to see if it can survive a 40 foot drop, just so she can be
a youtube sensation. My apple ipad is now destroyed and she has 83 views.

I know this is entirely off topic but I had to share
it with someone!

Responder

Admiring the time and energy you put into your site and in depth information you present.
It’s awesome to come across a blog every once in a while that isn’t the same out of date
rehashed information. Fantastic read! I’ve bookmarked your site and I’m including your RSS feeds to my Google account.

Responder

It’s remarkable for me to have a web page, which is helpful designed for my experience.
thanks admin

Responder

[url=http://healthsupl.com/]modafinil india cost[/url] [url=http://cialisdpill.com/]otc cialis 2019[/url] [url=http://cialisfmed.com/]cialis in canada online[/url] [url=http://cialisstep.com/]how to buy cialis without prescription[/url]

Responder

[url=https://sildenafilpharm.com/]can you buy viagra in australia over the counter[/url] [url=https://tadalafilbestbuy.com/]tadalafil canada price[/url] [url=https://dbpills.com/]can i buy metformin over the counter[/url] [url=https://cheapestsildenafil.com/]100mg sildenafil price[/url] [url=https://pviagra.com/]discount viagra canadian pharmacy[/url] [url=https://cialisdf.com/]buy cialis canadian pharmacy[/url] [url=https://sildenafilzpill.com/]sildenafil discount price[/url] [url=https://ivermectinpn.com/]cost of stromectol[/url] [url=https://pharmacyzeus.com/]your pharmacy online[/url] [url=https://viagrauni.com/]viagra 40 mg[/url]

Responder

[url=http://ivermectinbestbuy.com/]buy ivermectin stromectol[/url]

Responder