VBA Coding in Excel-help!

Posted 22nd Apr 2023

Hi

I need some help with VBA coding - appreciate any help.
So i have a single column in Excel with data in in. within this column some cells have no data whatsoever
I want to create a VBA button which only copies the cells with data (ignores blank cells).
so then i can paste these values into any other program be it notepad, word etc.

Many thanks

Post

Share with WhatsApp

Share with Messenger

Electronics Ask Software Software & Apps

New Comment

10 Comments

sorted by

Bob24601
22nd Apr
Can't you just add a filter to the column, filter out the blanks and then copy what's left?
Keep-it-Tea Author
22nd Apr
Yes, thts what i am doing at the moment but for multiple classes over multiple days thats a lot of clicks and selecting etc.

A VBA Button would select and copy the field, omitting the blanks in just one click.
tardytortoise
22nd Apr
what is the data type? e.g. dates, text, integers, decimals, etc

see this
howtogeek.com/761…el/

So copy the column to another column minus the blank cells - and then copy this column to notepad (edited)
Keep-it-Tea Author
22nd Apr
the data is just plain text.
which is copied from another column.

School register, but at the moment this column contains children who were in but also blank cells for those absent. This list needs forwarding by email or word or another app.

The task is monotonus as it means deleting individual blank cells.

Thought a VBA code which could only copy cells with the childs names would make it so much easier
lumsdot
23rd Apr
if possible ditch excel and use microsoft access
barneyb
23rd Apr
I was unaware that hukd was stackoverflow but there you go. Makes a change to Tokyo Laundry deals.

My idea for the coding is:

1. Create a variable for the data range
2. Declare an array/list/collection to store the values in
3. Loop through all cells in the data range variable. Use an if statement so only picks out values that are not blank. That is value <> ”" in VBA. Then add those value to the array/list/collection.
4. Then work out how you want to output the values in the array/list/collection. You'll need to loop through the array/list/collection and then put into whatever data structure you like.

Ensure you turn on option explicit, use the debugging tools and write the code section by section
Sc4mp0
23rd Apr
I feel like you're overcomplicating things.unless I'm misreading things.

If it's a single column and a straight forward copy paste of the data after the blanks are removed why not use the Unique formula?
mrcoxexcel
26th Apr
If you need further help please let me know. You'll need to send me the (sample) file and I'll do some simple coding for you - I'll need to give you my email address - so let me know.