Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

Como QUITAR VARIAS FILAS de LISTBOX Multiselect a la Vez Con ENTER en Excel VBA #512

Quitar Filas Listbox Multiselectcon Enter

Como SUPRIMIR VARIOS registros de Listbox MULTISELECT  a la Vez con ENTER

En este post se muestra una macro de Excel VBA que permite eliminar varios items de un Listbox Multiselect a la vez o en simultaneo al presionar ENTER, se requiere que se seleccione el o los elementos del listbox que se desean quitar o eliminar y se presione ENTER, de esta manera se quitarán todos los elementos seleccionados del Listbox Multiselect de Excel VBA.

Requieres mejorar tu manejo de planillas de cálculos de Excel, 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.

  
 

En el vídeo verás la macro en acción con 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.

Quitar Registros de un LISTBOX MULTISELECT VBA

Primero se debe descargar el libro de Excel que se usa de ejemplo, lo cual se podrá hacer del link que figura al final del post, al ingresar al libro de Excel se observará un botón verde, presionando el mismo se mostrará un formulario con un Listbox Multiselect.

El Listbox Multiselect se rellena con los datos que figuran en la hoja de Excel llamada Hoja 1 , para eliminar o quitar filas del Listbox, bastará solamente con marcar la o las filas que se requiera eliminar del Listbox de Excel VBA y presionar ERNTER, obteniendo como resultado que se quiten en forma simultanea todas las filas seleccionadas.

Es preciso recordar que solo se eliminan del Listbox Multiselect no de la hoja de Excel, si quieres saber como eliminar registros de la hoja de Excel también haz click en el link

 

Quizás también interese leer:

Como Eliminar Filas en Base a Color de Celda 
Como Eliminar Filas con Formato Negrita 
Como Eliminar Filas que Contengan Formulas 

 

Si quieres aprender más sobre listbox de excel o ver otros ejemplos que podrías aplicar a tus proyectos te invito a ver el playlist sobre Listbox de Excel.

Explicación del código QUITAR filas de Listbox Multiselect con ENTER

El código detecta cuando se presiona ENTER para ejecutar la macro, la macro consiste en recorrer todas las filas del listbox determinando cual está resaltada, marcada o seleccionada, procediendo a eliminar dicha fila, se recorren las filas del Listbox desde el Final hacia el principio del listbox, sin incluir la fila cero que es donde están los encabezados.

Para detectar cuando se presiona ENTER, la macro se tiene que colocar en el evento KeyPress del Listbox, luego comparar si la tecla presionada es igual a 13, en el código Ascii, ENTER, es representada por el número 13, el código usado es el siguiente:

Private Sub ListBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii = 13 Then

Una vez seleccionados los items o filas a eliminar y detectado que se presionó Enter se procede a realizar un bucle entre la última fila del Listbox y la fila 1 del Listbox de Excel, determinando que filas están resaltadas, se usa el siguiente código:

For x = Me.ListBox1.ListCount – 1 To 1 Step -1
If a.Selected(x) = True Then

Determinadas las filas resaltadas, en forma simultanea a medida que se ejecuta el bucle se van eliminando las celdas del listbox, con el siguiente código:

a.RemoveItem x



Descarga el ejemplo llamado como registros de Listbox Multiselect con ENTER en forma Fácil

Si deseas descargar el Libro Excel que se usa de ejemplo, lo podrá hacer desde el final del post, es totalmente Gratis y no tiene ningún tipo de restricción, solicito aportar a sostener la esta web si está dentro de tus posibilidades, desde ya muchas gracias.

Código que se encuentra en el modulo

Sub muestra1()
UserForm1.Show
End Sub

Código que se Encuentra en Formulario 3



Private Sub ListBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
On Error Resume Next
If KeyAscii = 13 Then
Set a = UserForm1.ListBox1
For x = Me.ListBox1.ListCount – 1 To 1 Step -1
If a.Selected(x) = True Then
a.RemoveItem x
End If
Next x
End If
End Sub

Private Sub TextBox1_Change()
On Error Resume Next
Set b = Sheets(«Hoja1»)
uf = b.Range(«A» & Rows.Count).End(xlUp).Row
If Trim(TextBox1.Value) = «» Then
‘Me.ListBox1.List() = b.Range(«A2:H» & uf).Value
‘Me.ListBox1.RowSource = «Hoja1!A2:D» & uf
uf = b.Range(«A» & Rows.Count).End(xlUp).Row
For i = 2 To uf
Me.ListBox1.AddItem b.Cells(i, 1)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 1) = b.Cells(i, 2)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 2) = b.Cells(i, 3)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 3) = b.Cells(i, 4)
Next i
‘Carga los datos de la cabecera en listbox
For ii = 0 To 7
UserForm1.ListBox1.List(0, ii) = Sheets(«Hoja1»).Cells(1, ii + 1)
Next ii

Exit Sub
End If

b.AutoFilterMode = False
Me.ListBox1.Clear
Me.ListBox1.RowSource = Clear
For i = 2 To uf
strg = b.Cells(i, 1).Value
If UCase(strg) Like «*» & UCase(TextBox1.Value) & «*» Then
Me.ListBox1.AddItem b.Cells(i, 1)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 1) = b.Cells(i, 2)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 2) = b.Cells(i, 3)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 3) = b.Cells(i, 4)
End If
Next i

‘Carga los datos de la cabecera en listbox
For ii = 0 To 7
UserForm1.ListBox1.List(0, ii) = Sheets(«Hoja1»).Cells(1, ii + 1)
Next ii

Me.ListBox1.ColumnWidths = «20 pt;90pt;80 pt;80 pt»
End Sub
Private Sub TextBox2_Change()
On Error Resume Next
Set b = Sheets(«Hoja1»)
uf = b.Range(«A» & Rows.Count).End(xlUp).Row
If Trim(TextBox2.Value) = «» Then
‘Me.ListBox1.List() = b.Range(«A2:H» & uf).Value
‘Me.ListBox1.RowSource = «Hoja1!A2:H» & uf
uf = b.Range(«A» & Rows.Count).End(xlUp).Row
For i = 2 To uf
Me.ListBox1.AddItem b.Cells(i, 1)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 1) = b.Cells(i, 2)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 2) = b.Cells(i, 3)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 3) = b.Cells(i, 4)
Next i
‘Carga los datos de la cabecera en listbox
For ii = 0 To 7
UserForm1.ListBox1.List(0, ii) = Sheets(«Hoja1»).Cells(1, ii + 1)
Next ii

Exit Sub
End If


If Len(TextBox2) > 2 Then
b.AutoFilterMode = False
Me.ListBox1.Clear
Me.ListBox1.RowSource = Clear
For i = 2 To uf
strg = b.Cells(i, 2).Value
If UCase(strg) Like «*» & UCase(TextBox2.Value) & «*» Then
Me.ListBox1.AddItem b.Cells(i, 1)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 1) = b.Cells(i, 2)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 2) = b.Cells(i, 3)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 3) = b.Cells(i, 4)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 4) = b.Cells(i, 5)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 5) = b.Cells(i, 6)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 6) = b.Cells(i, 7)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 7) = b.Cells(i, 8)
End If
Next i

‘Carga los datos de la cabecera en listbox
For ii = 0 To 7
UserForm1.ListBox1.List(0, ii) = Sheets(«Hoja1»).Cells(1, ii + 1)
Next ii

Me.ListBox1.ColumnWidths = «20 pt;90pt;80 pt;80 pt»
End If
End Sub

Private Sub UserForm_Initialize()
Dim fila As Long
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set b = Sheets(«Hoja1»)
uf = b.Range(«A» & Rows.Count).End(xlUp).Row
uc = b.Cells(1, Columns.Count).End(xlToLeft).Address
wc = Mid(uc, InStr(uc, «$») + 1, InStr(2, uc, «$») – 2)
With Me.ListBox1
.ColumnCount = 4
.ColumnWidths = «20 pt;90pt;80 pt;80 pt»
‘.RowSource = «Hoja2!A2:» & wc & uf
End With

‘Adiciona un item al listbox reservado para la cabecera
UserForm1.ListBox1.AddItem

uf = b.Range(«A» & Rows.Count).End(xlUp).Row
For i = 2 To uf
Me.ListBox1.AddItem b.Cells(i, 1)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 1) = b.Cells(i, 2)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 2) = b.Cells(i, 3)
Me.ListBox1.List(Me.ListBox1.ListCount – 1, 3) = b.Cells(i, 4)
Next i

‘Carga los datos de la cabecera en listbox
For ii = 0 To 7
UserForm1.ListBox1.List(0, ii) = Sheets(«Hoja1»).Cells(1, ii + 1)
Next ii

Application.DisplayAlerts = True
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.

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
512 ❤️ Como QUITAR VARIAS FILAS de ? LISTBOX Multiselect a la Vez Con ENTER en Excel VBA
Article Name
512 ❤️ Como QUITAR VARIAS FILAS de ? LISTBOX Multiselect a la Vez Con ENTER en Excel VBA
Description
Macro para ElIMINAR FILAS de Listbox Multiselect con ENTER 1) Selecciona los items a Eliminar 2) Presiona ENTER para quitar elementos del Listbox Multiselect 3) Los elementos no se borran de la hoja de Excel solo del Listbox 4) Ingresa y mira los detalles de la codificación.
macrosenexcel.com
macrosenexcel.com
macrosenexcel.com

Entradas relacionadas

Deja un comentario

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

Comentarios (30)

I am the owner of JustCBD label (justcbdstore.com) and I am currently aiming to expand my wholesale side of business. I am hoping someone at targetdomain can help me 🙂 I thought that the best way to do this would be to reach out to vape shops and cbd retail stores. I was really hoping if anybody at all could recommend a qualified site where I can purchase CBD Shops B2B Business Data List I am presently taking a look at creativebeartech.com, theeliquidboutique.co.uk and wowitloveithaveit.com. On the fence which one would be the very best selection and would appreciate any support on this. Or would it be much simpler for me to scrape my own leads? Ideas?

Responder

Для малого и среднего бизнеса. позиции верховного суда банкротство юридических лиц а так же ликвидация ооо с долгами

Responder

Can I simply just say what a relief to find someone who genuinely knows what they’re discussing on the net. You definitely understand how to bring a problem to light and make it important. More people ought to read this and understand this side of your story. I was surprised that you’re not more popular because you most certainly have the gift.

Responder

May I simply just say what a relief to uncover someone who genuinely knows what they’re talking about on the internet. You certainly know how to bring a problem to light and make it important. A lot more people really need to check this out and understand this side of your story. It’s surprising you are not more popular given that you most certainly possess the gift.

Responder

Hello! I simply wish to give you a big thumbs up for the great information you have right here on this post. I’ll be coming back to your website for more soon.

Responder

You should be a part of a contest for one of the greatest sites online. I most certainly will highly recommend this website!

Responder

This site was… how do I say it? Relevant!! Finally I have found something which helped me. Many thanks!

Responder

Spot on with this write-up, I honestly think this website needs much more attention. I’ll probably be back again to see more, thanks for the advice!

Responder

Everything is very open with a really clear description of the issues. It was definitely informative. Your website is very helpful. Many thanks for sharing!

Responder

I’d like to thank you for the efforts you’ve put in penning this blog. I am hoping to check out the same high-grade content by you in the future as well. In fact, your creative writing abilities has motivated me to get my own, personal website now 😉

Responder

Everything is very open with a precise description of the issues. It was definitely informative. Your website is very useful. Thank you for sharing!

Responder

You’re so cool! I do not suppose I’ve read through something like that before. So good to discover somebody with some genuine thoughts on this subject. Seriously.. many thanks for starting this up. This website is something that is needed on the web, someone with a bit of originality!

Responder

After I originally commented I appear to have clicked on the -Notify me when new comments are added- checkbox and now every time a comment is added I receive 4 emails with the same comment. There has to be a means you are able to remove me from that service? Appreciate it!

Responder

I was more than happy to discover this site. I need to to thank you for your time for this particularly fantastic read!! I definitely liked every little bit of it and I have you saved to fav to check out new information on your website.

Responder

After I originally commented I seem to have clicked on the -Notify me when new comments are added- checkbox and from now on whenever a comment is added I recieve 4 emails with the same comment. There has to be a way you can remove me from that service? Thanks a lot!

Responder

There is certainly a lot to learn about this subject. I love all the points you made.

Responder

I really like reading through a post that can make men and women think. Also, thanks for allowing me to comment!

Responder

Way cool! Some very valid points! I appreciate you penning this article and also the rest of the site is really good.

Responder

I’m impressed, I must say. Seldom do I come across a blog that’s both equally educative and amusing, and without a doubt, you have hit the nail on the head. The issue is something not enough people are speaking intelligently about. Now i’m very happy that I found this during my hunt for something relating to this.

Responder

Next time I read a blog, Hopefully it does not fail me just as much as this particular one. After all, I know it was my choice to read through, however I genuinely thought you would probably have something interesting to talk about. All I hear is a bunch of crying about something that you could possibly fix if you were not too busy seeking attention.

Responder

A fascinating discussion is worth comment. I believe that you should write more about this issue, it might not be a taboo subject but usually people do not speak about such issues. To the next! Kind regards!!

Responder

After looking over a few of the articles on your web page, I honestly appreciate your way of blogging. I saved it to my bookmark website list and will be checking back soon. Please visit my web site as well and let me know what you think.

Responder

Spot on with this write-up, I honestly believe that this amazing site needs far more attention. I’ll probably be back again to read more, thanks for the info!

Responder

I truly love your website.. Very nice colors & theme. Did you develop this website yourself? Please reply back as I’m wanting to create my very own website and would like to learn where you got this from or exactly what the theme is named. Cheers!

Responder

Howdy! I simply want to offer you a huge thumbs up for your great info you have got here on this post. I am returning to your web site for more soon.

Responder

Having read this I thought it was extremely enlightening. I appreciate you finding the time and energy to put this article together. I once again find myself spending way too much time both reading and posting comments. But so what, it was still worth it!

Responder

When some one searches for his vital thing, so he/she needs to be available that in detail, thus that
thing is maintained over here.

Responder

Thank you for another fantastic article. The place else may just anybody get that kind of information in such an ideal way of writing?
I have a presentation next week, and I am on the look for such info.

Responder

Excellent post. Keep writing such kind of info on your blog.

Im really impressed by it.
Hey there, You’ve done a great job. I’ll definitely digg it and for my part suggest to my
friends. I am confident they will be benefited from this website.

Responder

Good post. I learn something totally new and challenging on websites I stumbleupon every day. It’s always useful to read through content from other writers and practice a little something from their websites.

Responder