Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

Como borrar todo, formato, contenido, comentarios, hyperlink, validación


(adsbygoogle = window.adsbygoogle || []).push({});

Habitualmente cuando se automatiza una hoja de Excel a través de una macro, se tiene que borrar las celdas, en este post muestro una macro con las distintas formas de borrar celdas, ya que en algunos casos a lo mejor se requiere borrar todo lo que contenga la celda, datos y formatos; en otras quizás solo se precise borrar los formatos y preservar el contenido; es posible que se requiera borrar solo el contenido, el hyperlink o quizás la validación de la celda. Existen otros post relacionados con el formato de celdas por ejemplo como dar formato tamaño, tipo, negrita, cursiva, subrayado y color a la fuente de Excel, establecer color y bordes de celdas, determinar color de celdas, sumar celdas de un mismo color.

Antes de seguir recomiendo leer un excelente libro sobre Excel que te ayudará operar las planillas u hojas de cálculo, 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.
  

Si se precisa borrar todo lo que contiene la celda, es decir datos y formatos se  puede utilizar el siguiente código, lo que hace es borrar todo el contenido y formato de la celda C2:

Range(«C2»).Clear

Tal vez lo que se requiera es borrar el formato, pero no el contenido de una celda, en ese caso se debe usar:

Range(«C3»).ClearFormats

En caso opuesto al anterior es decir preservar el contenido y borrar el formato se puede utilizar el siguiente código.

Range(«C4»).ClearContents

Domina Excel Hoy

Es menos frecuente que los códigos presentados anteriormente, pero puede que se requiera borrar solo los comentarios, en ese caso utilizar:

Range(«C5»).ClearComments

Si en una celda se tiene un hyperlink y se desea borrarlo, se debe tener presente que existen dos opciones asociadas, una es borrar solamente el link sin afectar el formato clásico de un hyperlink es decir el color celeste y subrayado; la otra opción es borrar hyperlink y formato que lo caracteriza; el primer código borra solo el hyperlink conservando el formato; el segundo código borra todo, es decir hyperlink y formato, quedando en la celda la expresión literal del hypertexto o hyperlink.

Range(«C6»).ClearHyperlinks

Range(«C7»).Hyperlinks.Delete

Por último si se necesita borrar la validación solamente se debe usar:

Range(«C8»).Validation.Delete

Aporta si es posible con ello ayudarás a seguir manteniendo el sitio, descarga desde el link del final el ejemplo; suscribe al blog para recibir en tu correo todas las actualizaciones, dispones también de un canal de You Tube donde encontrarás explicaciones de macros con mayor detalle.

Suscribe a nuestro canal de You Tube para recibir en tu correo vídeos explicativos sobre macros interesantes, como  por ejemplo formulario que crea un listado de todas las hojas para poder luego seleccionarlas, buscar en listbox mientras escribes en textbox, ordenar hojas libro excel por su nombre, conectar Excel con Access y muchos ejemplos más.

function onYtEvent(payload) {
if (payload.eventType == ‘subscribe’) {
// Add code to handle subscribe event.
} else if (payload.eventType == ‘unsubscribe’) {
// Add code to handle unsubscribe event.
}
if (window.console) { // for debugging only
window.console.log(‘YT event: ‘, payload);
}
}

Código que se inserta en un módulo

Sub FormasDeBorrar()
Application.ScreenUpdating = False
    Range(«C2»).Clear
    Range(«C3»).ClearFormats
    Range(«C4»).ClearContents
    Range(«C5»).ClearComments
    Range(«C6»).ClearHyperlinks
    Range(«C7»).Hyperlinks.Delete
    Range(«C8»).Validation.Delete
Application.ScreenUpdating = True
End Sub

Sub EscribirCelda()
On Error Resume Next
Application.ScreenUpdating = False
    Range(«C2») = «Tomy Lee»
    Range(«C3») = «Dayra Col»
    Range(«C3»).Font.Color = 255
    Range(«C4») = «Tomy Lee»
    Range(«C4»).Interior.Color = 15773696
    Range(«C5»).AddComment («Prueba»)
    ActiveSheet.Hyperlinks.Add Anchor:=Range(«C6»), Address:= _
        «http://programarexcel.com», TextToDisplay:=»http://programarexcel.com»
    ActiveSheet.Hyperlinks.Add Anchor:=Range(«C7»), Address:= _
        «http://programarexcel.com», TextToDisplay:=»http://programarexcel.com»
    Range(«C8»).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=»=$F$6:$F$10″
Application.ScreenUpdating = True
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.


(adsbygoogle = window.adsbygoogle || []).push({});

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

Si te gustó por favor compártelo con tus amigos
If you liked please share it with your friends      

Entradas relacionadas

Deja un comentario

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

Comentarios (14)

I admit it, I’m an avid fan of the world
of seventies fashion and disguises. I love the ostentatiousness in this particular era, will be blatant disregard for subtlety or sophistication. Which decade can lay claim to flared trousers, concord collars and ‘big’ curly
coat? And which decade gave birth to disco music, and disco fashion forward?
Why the 70’s of coaching course!

The biggest differences so far is they let us really do what we wanted that’s not a problem album, as well as
set us each morning studio with number one choice
of producer.

Today, my taste is still Rock. I love Iggy, Steppenwolf, The Doors.
But I also listen to where to play 918kiss, White Stripes, Kanye, Pink, Katy Perry, Just a little
Keith Urban thrown in, some Jason Mraz, whatever.

Alice Cooper – He’s been selling tickets to sold-out shows for
50 years. The scratch golfer with the amazing live show comes towards UK in November to wow UK audiences
with songs like ‘No More Mr. Nice Guy’ and the classic, ‘School’s Out’.
When you’ve got a devotee base as massive as Alice
Cooper’s, worrying about selling concert tickets will never be something he’s used with.

So get your tickets today or risk failure!

The Cars. The ’70s-’80s pop-rock band with boatloads of hits has reunited,
and well worth the price pass up a to be able to see them, would shoppers?
The only bad news is that bassist Benjamin Orr shouldn’t
get with people today. The singer of ‘Drive’ and ‘Just What I Needed’ passed on in 3000.

Learn tips on how to cut dog’s fur. If I ever have 9-12 months
off I’m going left too, Do not think know whether it is called
beauty school, or hairdresser school however i would love to do why.
I feel like a good-looking lawn be the profession post Bayside.

We also have a blast, and surely has grounds there with Victory Records,
as well as have a whole friends there and these types of be fun times.
May a fun city and we are definitely looking toward being
normally.

It pays to subscribe for absolve to the Dallas Metal Music Examiner.
Manage this at the actual of the next few paragraphs for exclusive interviews,
concert announcements, reviews, and every item and fixture
metal. Specialists . also «LIKE» the San antonio Metal Music Examiner Facebook page, follow along on Twitter at Hawkeyedude93
look out his YouTube Procedure.

Responder

It’s an undeniable fact that a lot of that use search engines will only view the search results
on one couple of pages. In case you can make it on top of the firstt page, even better the top half of
the first page you are able to gget a higher probability of the link
being clicked on a. And you alreeady know that the more traffic for your site, a lot
more calories potential business you are blessed with.

Keywords. Use keywords to your owwn topic in your headline, opsning and closing paragraphs.Provided yoou can sprinkle them throughhout post that is great, device
better create naturally then spending many time getting as much exercise force in keywords!

CONTENT: This, again, is really a simple theme. Content is neecessary to organization. If a webpage
doesn’t have relevant or good content, generally as well take nicely the Internet.

Use these llow competition keywords in your blog post
title and repeat iit not longer than 5 times in your post’s delighted.

Search engine crawler will see your blog as another page. Its going to assume of
course as important. So it will rank yyou blog high.

When your blog goot ranked high, more men and women found doing
it. You get instant traffic from Goohle within early months
of blogging.

Try it yourself; consider a subject in which you’re interested.
Google it and see thee page. Test out your own reactions
to use first analyze. Did you feel like staying and reading always on? Did
you think » I’ve seen it all before.» and feel like it’ll comprise waste of theiir time to continue?

Or did you forget this article immediately and turn into at checking oout the?

If you experience writer’s block, try removing the topic you’re currentlyy writing about and commence a new people.

Keep it light annd simple, as you’re telling a frioend a floor.
Doing this will help you get back on track by means of the time you know it, you might have probably fiknished at least
10 aricles already.

The internet is just a bunch of files tend to bee linked as a group.
So links are usually essential. Sesrch engine optimization almost all about getting links to some website upp
hjgher in the search engine results piece. You want links tto expense.
You wanjt more links annd anything them from good internet sources.

If you have weblog make absolute to include it in your marketing methods.

Visit my web page – 3win8 slot

Responder

I’m amazed, I have to admit. Seldom do I come across a
blog that’s both equally educative and engaging, and let me tell you, you’ve hit the nail on the head.
The problem is an issue that not enough people are speaking intelligently about.
Now i’m very happy that I found this during my search for something regarding this.

Responder

Thanks for the marvelous posting! I genuinely enjoyed reading it, you’re
a great author.I will ensure that I bookmark your
blog and will come back in the future. I want to encourage yourself to
continue your great work, have a nice afternoon!

Responder

In fact, inn a startling set of pictures, you can see what’s happened to joker123.

The only funny part of the bit is thneir producer making wisecracks at teir
cost to doo business.

Also visit my homepage ocean king penavega

Responder

College Scorecard Report 19th Century Hungarian Authors

my page :: ebook (http://zcmanmj.shoppy.pl)

Responder

Vivekananda College Delhi University Ranking Early American Literature Books

Look at my blog post :: ebook

Responder

Whatt could bbe much better sipping an ice cold smoothie on a
sweltering summer day? Smoothies love more and more popular in our increasjngly health conscientious world and the correct aswer is simple to make them at home.
You ccan create a smoothie with juust about any ingredients you could quite possibly have on hand for a portion of the cost of what
you wiol pay at a Smoothie Bar. Andd when you consider that
a smoothie is usually much healthier than other desserts, you
realize that you have got yourself one great indulge!

There are three major search engines namely Google, Yahoo and MSN.
Submit all your site for innclusion in those 3 majuor search and keep submitting your site pages additional search
engines on consistently. Do it manally anyone may apply certain search engine submission software or service which wijll let you by automatic submission.

Well, there isn’t any a way you caan go about promoting your prodcuts online
without having a website. You can actually go and produce a blog and generate a great deal of of money
from it in a couple off ways actually. You can write blogs about
certain prodchuts your promoting and make your affiliaye linkls
in the bottom of yoour blog so that people can click through andd opt for thhe product.

There extra effectve solution to brng popularity for your website and that
can bbe done thfough link flow. Link exchange can result from a good
page rank for web site and can improve thee various search
engines ranking.

You require to think like a normal person whoo is using internet and show off engines to obtain answers to everyone his headaches.
Keywordfs can prove to be tthe biggest kkey factors in having the first place oon Google Search Page.
The keyword in order to very common, related to aall your business/ business site.
A well experiences Sezrch engine optimization firm can a person in here.
Consider SEO India , for less expensive search
engine optimization service for your business.

French Liick Springs Resort Brings Aspire to Orange
County, Indfiana – Also from September of 2007. One ttime caught
my attention because I am Larry Bird fan and he lived in French Riff.
Her article is centered on the town, not Lewis!

I may go on for the days wrriting about the way you probably
will make money with a blog, butt my goal here was to give you options to lkok
into and hopefully offer you with the actual to started out.

Takee a look aat my webpage sky777 casino

Responder

Which means you might possess a scenario which follows
this path using your website. This is sole way to win the unsecured credit card game.

Second in review will be the Roman, by Tony Perotti.

Responder

Ssci Journal List International Relations English
Literature Authors

Here is my site … pdf (jwxban.shoppy.pl)

Responder

Generally People is short of lot of time and energy for
that. This can a person to uncover possiible cloaking issue
that may be taking position. The landing page is key in majing cash.

Responder

Faiiry Garden Slots: Here is thee third slit
game introduced inn the DoubleDown Casino this summer.

That top casino bonuses and free spin that can assist you to enhance your winning dimensions.

Responder

Marxism And Literature Williams Junie B Jones Books How Many Are There

Here is my web site ebook

Responder

Ecec Maroochydore National University College San Juan Pr

my web blog: best books

Responder