No Spaces with VBA

[excelhasey]

Good afternoon all,

In an Excel sheet I have a tab LiveData (sheet8) and within this tab there is a column of information that is constantly updated but sometimes there might be no data to add to a cell so it will be blank i.e.a2=6, a3=8, a4=blank, a5=1.5, a6=blank, a7=3 etc now I want another column (say column D of this tab) to list all the data from column A but without the blanks, so i.e.d2=6, d3=8, d4=1.5, d5=3 etc hence all the blank cells have been removed

Each event with change the data in Column A and also it is not known which cells will contain data and which will be blank, so how do I do this ??

How would you write this in VBA so it cleared all previous data in column D on opening the spreadsheet and then set about copying my data without the blanks ??

Any ideas anyone ??

[Captain Sensible]

Why don't you copy it all and just use regex to remove the blanks before it's written back to whatever cell you want.

[Captain Sensible]

I suppose a lot depends on what vba code you already use in there for where it can be slotted in. You could just use a range="" to clear any previous data and if you've some loop already in there have it check the data, if not blank then use the End(xlUp).Offset(1, 0) code to stick it on the sheet at the next available cell

[excelhasey]

Sorry Captain no idea what regex is

There is also a bit of VBA already in there pulled from advise of different people so I wouldn't know how to add End(xlUp).Offset(1, 0) correctly sorry

Do I just write End(xlUp).Offset(1, 0) ?? - How does in know which range I want it to remove the blanks from

Any chance of just adding a load of numbers in an excel sheet column A with some blanks in there and then and getting it to do another column copying the previous data but with no blanks and PM me so I can see how you have done it ??

If so can you save it as an excel file compatable with 2007 ??

[Captain Sensible]

Try this subroutine in a sheet and see if it'll do for you.

It just copies the values from A1 to A50 into D1 to D50 then removes the blanks

Code: Select all

Sub DeleteEmptyCells()
Range("D1:D50").Value = Range("A1:A50").Value

Range("D1:D50").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp

End Sub

[excelhasey]

Could be me but I put code in a brand new blank workbook and added random data and blanks in column A on sheet 1 then added your code but nothing appears in column D sorry

[Captain Sensible]

You'll need to run the macro for it to do anything

[excelhasey]

Ok sorry yes that works and does what it says on the tin nice one thanks but ............

How do I get the excel worksheet to run the macro for me everytime any cell is changed / updated etc ??

[excelhasey]

Thanks Captain your a star I think I have done it now by using

Application.EnableEvents statements

Cool I have actually sorted a VBA problem myself - I think ........

Thanks mate

[Captain Sensible]

Nice one, after a while it kind of all sinks into place, just have to pay attention to where you put snippets of code so things happen at the right time rather than after the event and avoid your code going into loops