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

[bearcat329]

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!

 

[Fazza]

Hi, Bearcat. I used the macro recorder, then tidied up. HTH. Regards

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]

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!