Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

Como Eliminar Filas Excel con Formato Negrita #471

Eliminar Filas Excel con Negritas

En el post se muestra Como Eliminar en Excel que contengan formato Negrita en en la celda analizada, se recorre todas las filas de la primer a la última en busca de las filas que coincida con el criterio de búsqueda, detectadas dichas filas son eliminadas.

Si quieres saber más sobre como eliminar filas en excel con criterio mira el siguiente playlist que tratan diversas formas de eliminar filas en excel en base a criterios ya sea filas con formulas, formatos, con determinadas palabras y mucho más.

Para manejar Excel en forma eficiente haz 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.

El vídeo verás la macro en acción con una explicación más detallada, suscribe a nuestro canal de You Tube para recibir en tu correo vídeos explicativos sobre macros interesantes, como  por ejemplo Recorre fila buscando y comparando datos de dos columnas en hojas distintasbuscar en listbox mientras escribes en textboxcomo crear una factura o sale invoice y grabar guardar PDF XLS y enviar por mailtrabajando con filas, celdas, columnas, rangos y muchos ejemplos mas.

Como funciona el ejemplo practico de la macro como suprimir filas excel con formato negrita

Descargar el archivo, que se puede hacer desde el final del post, al abrir el libro de excel se muestran dos botones uno «Eliminar Fila» que ejecuta la macro para quitar las filas con formato negrita o bold en excel y otro «Ejecutar Nuevamente» para restablecer la base de datos para poder ejecutar la maco una y otra vez.

La macro hace un bucle recorriendo todas las filas desde la primer a la última fila con datos, el recorrido de las filas lo realiza de abajo hacia arriba, determina si la celdas de la columna y fila analizada tiene formato negrita, en ese caso suprime o elimina la fila de excel que coincide con el criterio establecido, en este caso tener formato negrita.

Explicación del código que permite a la macro eliminar filas de excel en base a criterio

Primero se determina cual es la última fila con datos, lo cual se hace con el siguiente código:

uf = Range(«A» & Rows.Count).End(xlUp).Row

Luego se realiza un bucle entre la primer y última fila con datos determinada anteriormente, con la particularidad que se recorren las filas de abajo hacia arriba.

Esto es así porque si se elimina una fila el resto de filas sube y el bucle sigue con la siguiente fila por ende salta la fila por ejemplo 19 que ahora es fila 18 al ser eliminada la original fila 18.

Que sucede en el caso de que la fila 19 que ahora es 18 también deba ser eliminada, porque coincide con el criterio de eliminación de filas, si se recorrieran las filas de arriba hacia abajo y se da el caso planteado las filas se saltearían y no eliminaría filas que deberían ser eliminadas.

Si bien se puede saltar este problema se debe agregar más códigos, por ello es más sencillo hacerlo así es decir recorrer las filas de abajo hacia arriba y ello se consigue con el siguiente código:

For x = uf To 2 Step -1

Para establecer si la filas de excel se debe eliminar se determina si la celda analizada de la columna y fila correspondiente contiene formato negrita ello se determina así:

If a.Cells(x, «A»).Font.Bold = True Then

En caso de ser afirmativo es decir la celda contiene formato negrita se procede a eliminar la fila de excel con formato negrita de la siguiente manera:

a.Cells(x, «A»).EntireRow.Delete

Por último la macro termina emitiendo un mensaje, Msgbox (click para aprender más sobre Msgbox) y mostrando la cantidad de filas eliminadas cuya cuenta fueron acumulándose en la variable conta, así

conta = conta + 1

MsgBox («Se copio la base de datos nuevamente»), vbInformation, «AVISO»

Código y descarga del ejemplo como suprimir filas en excel con formato negrita

Código que se inserta en un módulo de Excel

Sub EliminaFila()
Application.ScreenUpdating = False
On Error Resume Next
Set a = Sheets(«Hoja1»)
uf = Range(«A» & Rows.Count).End(xlUp).Row
For x = uf To 2 Step -1
If a.Cells(x, «A»).Font.Bold = True Then a.Cells(x, «A»).EntireRow.Delete: conta = conta + 1
Next x
MsgBox («Se eliminaron » & conta & » registros»), vbInformation, «AVISO»
Application.ScreenUpdating = True
End Sub

Sub DeNuevo()
Set a = Sheets(«Hoja1»)
uf = Range(«A» & Rows.Count).End(xlUp).Row
a.Range(«A1:G» & uf).Clear
Sheets(«Hoja2»).Range(«A:G»).Copy Destination:=a.Range(«A1»)
MsgBox («Se copio la base de datos nuevamente»), vbInformation, «AVISO»
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.

Summary
product image
Author Rating
1star1star1star1star1star
Aggregate Rating
5 based on 1 votes

Entradas relacionadas

Deja una respuesta

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

Comentarios (23)

Excellent article. I will be experiencing some of these issues as
well..

Responder

First of all I would like to say awesome blog! I had a quick question in which I’d like to ask
if you do not mind. I was interested to know how you center yourself and clear your head before writing.
I have had a tough time clearing my mind in getting my thoughts out.
I truly do enjoy writing however it just seems
like the first 10 to 15 minutes are generally lost just trying to
figure out how to begin. Any suggestions or hints? Cheers!

Responder

In fact when someone doesn’t understand afterward its up to other visitors
that they will help, so here it takes place.

Responder

Hmm is anyone else experiencing problems with the
pictures on this blog loading? I’m trying to determine if its a problem on my
end or if it’s the blog. Any suggestions would be greatly appreciated.

Responder

I love your blog.. very nice colors & theme. Did you design this website yourself or did you hire someone to do it for you?
Plz answer back as I’m looking to construct my own blog and
would like to find out where u got this from. kudos

Responder

This article offers clear idea in support of the new visitors of blogging, that genuinely how to
do blogging.

Responder

Good day! I know this is somewhat off topic but
I was wondering if you knew where I could locate a captcha plugin for my comment form?
I’m using the same blog platform as yours and I’m having trouble
finding one? Thanks a lot!

Responder

Thank you for any other informative site. Where else may just I get that kind of information written in such a perfect approach?

I have a mission that I’m just now operating on, and I’ve
been at the look out for such information.

Responder

Nice post. I learn something new and challenging on websites I stumbleupon everyday.
It’s always exciting to read articles from other writers and
practice something from other web sites.

Responder

Greetings! Quick question that’s completely off topic.
Do you know how to make your site mobile friendly?
My site looks weird when viewing from my apple iphone.
I’m trying to find a template or plugin that might be able to fix this
issue. If you have any recommendations, please share.
Cheers!

Responder

Hello, i feel that i noticed you visited my web site
thus i got here to return the favor?.I am attempting to to find issues to improve my website!I guess its good
enough to make use of some of your ideas!!

Responder

What’s up to every single one, it’s actually a fastidious for
me to pay a visit this site, it includes helpful Information.

Responder

I know this web page presents quality depending articles and additional stuff, is there any other site which gives these kinds of stuff in quality?

Responder

I think this is among the so much vital info for me. And i’m happy studying your
article. However wanna commentary on some normal things,
The website taste is perfect, the articles is in reality excellent
: D. Just right task, cheers

Responder

Hi it’s me, I am also visiting this web site regularly, this
website is genuinely pleasant and the users are in fact sharing
pleasant thoughts.

Responder

Definitely believe that which you said. Your favorite
reason appeared to be on the net the simplest thing to be aware of.
I say to you, I definitely get annoyed while people consider worries that they just don’t know about.
You managed to hit the nail upon the top and also defined out the
whole thing without having side effect , people can take a
signal. Will probably be back to get more. Thanks

Responder

Wow, amazing weblog structure! How long have you been blogging for?
you made running a blog look easy. The whole glance of your site is
great, as neatly as the content!

Responder

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

Responder

I do not know whether it’s just me or if perhaps everyone else encountering issues with your website.
It appears like some of the written text in your posts are running off the screen. Can someone else please comment and let me know if this is happening to them as well?
This might be a issue with my internet browser because I’ve
had this happen before. Many thanks

Responder

This is the right webpage for everyone who really wants to understand this topic.
You understand a whole lot its almost tough to argue with you (not that I actually will
need to…HaHa). You definitely put a fresh spin on a subject which has been discussed for decades.
Excellent stuff, just great!

Responder

Hey there! Quick question that’s entirely off topic. Do you know how to
make your site mobile friendly? My website looks weird
when browsing from my apple iphone. I’m trying to find a template or plugin that might
be able to fix this issue. If you have any recommendations, please share.
Cheers!

Responder

I have been surfing online greater than 3 hours as of late,
but I never found any interesting article like yours.
It’s lovely worth sufficient for me. In my view, if all webmasters and bloggers made good content material as you probably did, the net shall
be much more helpful than ever before.

Responder

Piece of writing writing is also a fun, if you be acquainted with afterward
you can write or else it is difficult to write.

Responder