Array to remove blanks from Range, how do I apply the range back to my sheet??

[seanpears99]

Hi All

Not really a great VBA coder as you will be able to tell.

I have a user form with some selection criteria, when a partiular status is selected I pricess my range of data and set the non wanted rows to be a null value and write this data back into the range.

How do I then remove the blank lines from my temp worksheet, I was trying to use a filter but this wasnt working.

Any ideas?


'PROJECT STATUS SELECTION
'Data range
Set rngdata = Range("AB2", Range("AB" & Rows.Count).End(xlUp))

'Read data into an Array
v = rngdata.Value

'add the selected ListBox items to a Dictionary object
Set Dict = CreateObject("Scripting.Dictionary")
Dict.CompareMode = 1 'Text compare mode
With ProjStatus
For f = 0 To ProjStatus.ListCount - 1
If .Selected(f) Then
Dict(.List(f)) = f
End If
Next f
End With

'Test each item in the data array if it exists in the Selected items Dictionary
For f = LBound(v) To UBound(v)
'Items that that are not selected are cleared (vbNullstring) from the data array

If Not Dict.Exists(v(f, 1)) Then v(f, 1) = vbNullString
Next f

'Write the data array back to the data range
rngdata = v

NOW I HAVE THE AMENDED RANGE I WANT TO REMOVE THE NULL VALUED ROWS FROM MY SHEET BY DELETING THEM

Cheers
Sean

[apo]

Hi..

Something like...

Please Login or Register  to view this content.

Is that what you're after?

[kalak]

would you like to try this ON SOME TEST DATA and see if it works for you. modifications to your own code are in red

Please Login or Register  to view this content.

[seanpears99]

Hi..

Something like...

Please Login or Register  to view this content.

Is that what you're after?

Hi Apo,

Sorry its not just blanks from the sheet I want to clear, I have set the unwanted records to blank in the array and copied back to the range, I need to remove the lines from the worksheet that relate to the null values.

Thanks for the help tho
Sean

[seanpears99]

would you like to try this ON SOME TEST DATA and see if it works for you. modifications to your own code are in red

Please Login or Register  to view this content.

Hi Kalak

Tried the code and copied the range to another sheet but its not what I am trying to do. I have set the unwanted records to blank in the array and copied back to the range, I need to remove the lines from the worksheet that relate to the null values.


Sean

[apo]

Hi..

Second stab at it..

I am thinking you could add each row to a "Delete" range using Union and delete all those rows at the end by deleting the "Delete" range.

Most of the red text is where the changes are..

Please Login or Register  to view this content.

[seanpears99]

Hi..

Second stab at it..

I am thinking you could add each row to a "Delete" range using Union and delete all those rows at the end by deleting the "Delete" range.

Most of the red text is where the changes are..

Please Login or Register  to view this content.

Code.jpg

I have attached a screen shot of the rngdata watch, for any record where there is a blank I want to delete the line in the worksheet, my rngdata is only one column AG which is where I do the dictionary check and null everything else that doesnt match.

[apo]

Hi..

This is what I get..

Range.png
Watch.png
RangeafterDelete.png


As you can see.. after the range (which is composed of the rows that have a Null value) is deleted.. only the 2 rows that had the Selected value are left...

Isn't that what you want? or am I missing something..

Note: I used column A for the range.. but that is irrelevant..