Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

Insertar columnas

Insertar columnas con VBA  ó macros, me preguntaron en un foro de Excel como se insertaban columnas teniendo en cuenta los meses del año, como así también que el informe original no otorgara datos de meses en los cuales no había movimientos, por ello era necesario agregar columnas en base a los meses que faltaban y a su vez se tenía  que rellenar con ceros, en este ejemplo se insertan tantas columnas como sean necesarias  para que queden en orden correlativo del 1 al 12, simulando los meses, determina  cual es la última fila con valores de todas las columnas, en base a ello rellena hasta esa fila con ceros si no hay valor  que sea distinto de éste, en el link del final se encuentra el archivo que puedes descargar y adaptar a tus necesidades; te recomiendo que hagas click y leas un  excelente libro sobre el tema; aquí va el código:

Sub insertacol()
Application.ScreenUpdating = False
Dim fila, col, x, uf, ufmayor, dato As Integer
fila = 1
col = 1
x = 0
For x = 1 To 12
      dato = Sheets(«hoja1»).Cells(fila, x).Value
   If dato <> x Then
      Sheets(«hoja1»).Cells(fila, x).EntireColumn.Insert
      Sheets(«hoja1»).Cells(fila, x) = x
      Sheets(«hoja1»).Cells(fila, x).Select
    
      With Selection
           .NumberFormat = «000»
           .Font.Bold = True
      End With
   End If
   ‘col = col + 1
Next x
x = 0
For x = 1 To 12
     ‘Determino la última fila vacia
    uf = Sheets(«hoja1»).Cells(Rows.Count, x).End(xlUp).Row
    If uf > ufmayor Then
    ufmayor = uf
    End If
Next x
x = 0
‘Recorre columnas
For x = 1 To 12
     ‘Recorre filas
     For j = 2 To ufmayor
          ‘Determino cual es la columna que tiene la fila maxina de datos
          ‘Con ello se hasta donde rellenar con ceros, siempre que la celda este vacía
          If Sheets(«hoja1»).Cells(j, x) = Empty Then
             Sheets(«hoja1»).Cells(j, x) = 0
          End If
    Next j
   
Next x
Application.ScreenUpdating = False
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 una respuesta

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

Comentarios (11)

Furthermore, the attire might be accessorized with beads neckpieces,
gold chains, chunky watches, stone embellished belts, suede footwear, leather luggage and perhaps anything beneath the solar.
If we glance around, we’ll see that the t-shirt is without doubt one of the most commonly
worn items of clothes. So it is time to gountucked and add spice to your life.
Printed t-shirt with the help of trendy printing equipments,
t-shirt with cheesy traces, embroidered t-shirt, t-shirt with the quick sleeves, button-much less or collarless t-shirts, v- neckline and polo
neck t-shirt in several colours and patterns- you name it and you bought
all of it. It’s chosen by people no matter
their gender, religion, occupation or age bracket.
It can be easily paired with pyjamas (unfastened pants), cotton trousers, jeans, linen fabrics,
polyester, fantastic grade wool and any other materials or blended fabric.
The wearer just wants to make use of his sense of discretion, know when to put
on what and see to it that the look is balanced, and he
is able to step out of the house. T-shirts can be found in varied textures and could be clubbed
with a go well with or stuff like jackets, cardigans, shawls, stoles, and so forth.

And that isn’t all. It’s low on upkeep, too. It is comfortable and actually versatile.
Revive your wardrobe and make a style statement with a contemporary set of t-shirts.

Responder

Hello! Do you use Twitter? I’d like to follow
you if that would be ok. I’m undoubtedly enjoying your blog and look forward
to new posts.

Responder

I do not know if it’s just me or if everyone else encountering problems with your website.
It appears like some of the text in your posts are running off the screen. Can someone else please provide feedback and let me know if this
is happening to them as well? This might be a problem with my
web browser because I’ve had this happen previously.
Appreciate it

Responder

Having read this I thought it was very enlightening.I appreciate you finding the time and energy to put this article together.I once again find myself personally spending a significant amount of time both reading and leaving comments.But so what, it was still worthwhile!

Responder

Hi there! Would you mind if I share your blog with
my facebook group? There’s a lot of folks that I think would really appreciate your content.

Please let me know. Thank you

Responder

En yasaksı bayan anadolu yakası escort olmamdan bence
etkileneceksin.
Tamamen sınırsız olduğum söylenemez çünkü birleşme sırasında
daima prezervatif kullandırırım.
Bu sadece benim anadolu yakası ecort bayan sağlığımı korumakla alakalı değil, sizlere değer verdiğimdendir.

İçin de prezervatif şarttır ama

Responder

Somebody necessarily assist to make severely articles I might state.
This is the very first time I frequented your web page and to this point?
I amazed with the analysis you made to create this particular
post incredible. Fantastic task!

Responder

I know this if off topic but I’m looking into starting my own weblog
and was curious what all is needed to get set up? I’m
assuming having a blog like yours would cost a
pretty penny? I’m not very web smart so I’m not 100% sure.
Any tips or advice would be greatly appreciated. Thanks

Responder

Wonderful goods from you, man. I’ve remember your stuff previous to and you are simply too wonderful.
I actually like what you’ve obtained here, really like what you are
saying and the best way during which you are saying it.
You’re making it enjoyable and you still take care of to stay it smart.
I can’t wait to learn far more from you. That is actually a great site.

Responder

Now I am going away to do my breakfast, later than having my breakfast
coming over again to read other news.

Responder

Thanks designed for sharing such a pleasant opinion, piece of writing is
nice, thats why i have read it fully

Responder