Is there no easy way to delete spaces with VBA
I usually Using this code to delete the space:
Option Clear sub DeleteBlanks () Dim intCol for intCol as integer = 1 to 14 'cols A to D range (cells) (2, intCol), cells (146521, intCol)). _ Special Room (xlCellTypeBlanks) Delete Shift: = xlUp Next Entry Margin Sub But my position is a bit special and I can not use this code. My table looks like this but with more columns:
Item code Description Cond1 Cond2 .... an ID1 alpha1 box 10 box b id 1 bravo 1 box 50 box c id 1 charley 1 box d id 1 delta 5 box e id 1 echo 2 box 20 Box F ID 1 Foxtrot 1 Box 40 Box G ID 1 Golf 1 Box 20 Box And I like this:
Item Code Description Cond1 Cond2 ... an ID1 alpha1 box 10 box b id 1 bravo 1 box 50 box c id 1 charley 1 box d id 1 delta 5 box e id 1 echo 2 box 20 box f id 1 foxtrot 1 box 40 box g id 1 golf 1 box 20 box target column is not to delete the blank place from COnd 2 etc., But only to put it in order when ITEM is not coded and on the same line I hope that I was quite clear if it is possible to easily change the number of columns or lines, it would be great and this is the same answer, but with the ITEM code and the rearrangement from the details.
If I am not enough, please ask me for more information. Thanks in advance. James
If you fill in the values in the first column then you delete some of the code Can be used . The following code will fill a selected category with the values above the empty
subfill_in_blanks () fills in the empty space with the values above 'this macro empty cell', then it To convert special values from one formula to all the cells, set a range as myRng dimension myRng = Selection myRng.SpecialCells (xlCellTypeBlanks). Select the selection. FormulaR1C1 = "= R [-1] C" myRng. Select the selection. Copy selection. Paste Special Paste: = XLPist Value, Operation: = XLonone, Skip Blocks _: = False, Transges: = False and All Then you select the column 2 and using this code, Rows that can delete rows are empty columns 2
sub-del_blank_zero () Range set range as slow range Range as mycell Range = RNG for each mycell selection In if mycell = 0 or mycell = "" then Mycell.EntireRow.Delete end 'deb Please. Print mycell.Address next mycell end sub Delete the code I modified from your questions below Just select the first column and then run the code, it will be on the sample you provided Works, I have not used the offset of 10, if your data is wide or narrow then you can change that value
sub del_blank_zero () Dim RNG as the range dim Range set as mycell RNG = Range for each mycell selection If mycell.Offset (0, 1), = 0 or mycell.Offset (0 1) = "" Then Range (mycell offset (0, 1), mycell.Offset (0, 10)). Delete shift: = xlUp end if microsoft = 0 or mycel = "" then mycell.Delete Shift: = xlUp end if the next msec and all 
Comments
Post a Comment