VBA - Copying array of data to new columns, excluding blank cells

bearcat329

New Member
Joined
Apr 11, 2013
Messages
3
I am relatively new to VBA, but I have been stuck on one issue for a few days now. I am unable to post the actual excel workbook that I am using, so bare with me, I will create an example if I am unable to thoroughly explain what I am looking to do, I really think this is an easy solution I just do not have the VBA knowledge at this point.

I am using a userform that allows the user to select certain options, which will grab data from certain sheets dependent on their selection and put it in a results sheet. This selected data is printed in N3:N59 and O3:O59 on Sheet 4, depending on the selections made by the user not all of these cells will be filled in. But the selected data has to match up with other stuff, so I need this big table first, before transferring to a smaller one. Because of the fact not all cells will be filled due to not being desired by the user I want to copy the data that was selected to a more compact table that only includes the selected data and will get rid of the blank cells. This table will go to G3:G59 and H3:H59 (dependent on the size, obviously will not fill that entirely). Both columns will have the same amount of blanks/in the same row, N and O columns are basically going to be x and y for plotting, so there is not an issue of different blanks or mismatching, they match up with one another and should be copied/pasted together.

I need this to be done programatically in VBA, I need this to all be done in one step via VBA so using the sort/filter is not an option.

I will create an example while I am waiting for a reply, was hoping this would be simple enough I wouldnt need that.

Thanks in advance!
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Hi, Bearcat. I used the macro recorder, then tidied up. HTH. Regards

Code:
Sub from_macro_recorder()
  Selection.Copy
  Range("G3").Select
  ActiveSheet.Paste
  Selection.SpecialCells(xlCellTypeBlanks).Select
  Application.CutCopyMode = False
  Selection.Delete Shift:=xlUp
End Sub


Sub rewritten()
  Range("G3:H59").ClearContents  'extra step to clear previous data
  Range("N3:O59").Copy Range("G3")
  Range("G3:H59").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
End Sub
 

bearcat329

New Member
Joined
Apr 11, 2013
Messages
3
Thank you so much that worked beautifully once I specified the Sheet associated with the Ranges. I knew it was gonna be simple. Thanks again!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,481
Messages
5,601,918
Members
414,482
Latest member
morkar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top