VBA help with filling empty rows with data

[dilla1988]

Hello,
I'm new and I'm not an expert on VBA and macros.

I have a file that has about 55000 rows and I need to sort the data out and get them out on a csv file that has its own format.

Now my data look like this.


fgs dfsdf sdfs
GSD DSF dfgf



2 5 7 7 9 0 0 6
2 5 7 7 9 0 0 52
2 5 7 7 9 0 0 52

2 5 7 7 9 0 0 52
2 5 7 7 9 0 0 51
2 5 7 7 9 0 0 52
2 5 7 7 9 0 0 54

2 5 7 7 9 0 0 54
2 5 7 1 9 0 0 54
end


Now imagine the numbers are in consecutive cells. See the empty row between numbers. I want a code where it searches through the second half of the data, and check for empty row, pick the entry from top , insert in that empty space and loop till end.

so it would look like:

fgs dfsdf sdfs
GSD DSF dfgf



2 5 7 7 9 0 0 6
2 5 7 7 9 0 0 52
2 5 7 7 9 0 0 52
fgs dfsdf sdfs
2 5 7 7 9 0 0 52
2 5 7 7 9 0 0 51
2 5 7 7 9 0 0 52
2 5 7 7 9 0 0 54
GSD DSF dfgf
2 5 7 7 9 0 0 54
2 5 7 1 9 0 0 54
end

Please help me out guys

thanks a lot

 

[tyger0951]

This should get you started. Post some of your own code and any errors you run into for further help

Sub Test () 


Dim Cell as Range
Dim I as Interger


I = 1 'So that below code starts at A1 first  


For Each Cell in Range("A5:A20") 'goes through each cell from A5 through A20
    
    If Cell.value = ""  Then  'Checks if blank cell  
    
        Cell.value = Range("A" & I ).Value 'If blank fills in with The next item from A1
    
            I = I + 1 'Adds 1 to I so on next blank cell it takes A2     
    
    End If


Next


End Sub

Good Luck

 

[dilla1988]

Works great Tyger! Thanks a bunch.

Anway if I want to expand the range say A5:E20 and check for empty rows in that range, then fill the data from top that are in A1:B4, how should I go about it?

So this time rather than one entry from top it's taking a row of data and inserting in the empty rows below.

I've been playing around with this and below is something I was working on. It just copies but i cant get it to paste. plus it's not moving to next line.

Sub test1()
Dim rCheck As Range, Rng As Range
'Set Range to rcheck
Set rCheck = Range("B1:B47")
'Loop through and check each cell
For Each Rng In rCheck
If Rng = "" Then
Rng.Value = Range("E1:H1").Copy

' what to go here ?


End If
Next
End Sub

This should get you started. Post some of your own code and any errors you run into for further help

Sub Test () 


Dim Cell as Range
Dim I as Interger


I = 1 'So that below code starts at A1 first  


For Each Cell in Range("A5:A20") 'goes through each cell from A5 through A20
    
    If Cell.value = ""  Then  'Checks if blank cell  
    
        Cell.value = Range("A" & I ).Value 'If blank fills in with The next item from A1
    
            I = I + 1 'Adds 1 to I so on next blank cell it takes A2     
    
    End If


Next


End Sub

Good Luck

 

[tyger0951]

Give this a shot

Sub Test () 


Dim Cell as Range
Dim I as Interger


I = 1 
'So that below code starts at A1 first 


For Each Cell in Range("A5:A20") 'goes through each cell from A5 through A20 
If Cell.value = "" Then 'Checks if blank cell 
Range("A" & I ,Range("E" & I )).Copy 
cell.pastespecial
 I = I + 1 'Adds 1 to I so on next blank cell it takes A2 
 End If 
 Next
 End Sub

 

[tyger0951]

If issues may want to move to

Range("A") & I ).EntireRow.Copy 
Cell.PasteSpecial

Give this a shot

Sub Test () 


Dim Cell as Range
Dim I as Interger


I = 1 
'So that below code starts at A1 first 


For Each Cell in Range("A5:A20") 'goes through each cell from A5 through A20 
If Cell.value = "" Then 'Checks if blank cell 
Range("A" & I ,Range("E" & I ).Copy 
cell.pastespecial
 I = I + 1 'Adds 1 to I so on next blank cell it takes A2 
 End If 
 Next
 End Sub

 

[dilla1988]

WORKS PERFECT!

now the last step. Using offset command how can I paste the the row two cells to the left (same row as before).

Thanks heaps tyger!!

If issues may want to move to

Range("A") & I ).EntireRow.Copy 
Cell.PasteSpecial

 

[tyger0951]

Cell.offset(0,2).pastespecial

Because

.Offset(RowOffset, ColumnOffset)

Since you have dim cell as range. Type cell. Then hit ctrl + space bar. You'll see offset as option to choose when you start to type.



Range.Offset Property (Excel)

 

[dilla1988]

Thanks Tyger. works well! just had to change it to (0.-2) to move left.

Well im still testing this on a small scale file. As I have a one with 55500+ cells I will only get one go! :O

anyway what's the quickest method to clear the content that just got filled. becasue ctrl + z wouldn't do.

I have see clearcontents fucntion. how does it work?

thanks man!

 

[tyger0951]

Data moved or add with vba code not be undone. Ctrl z does not work. You should always be running test codes on copies of data if not could lose it all.

No quick way. Since code placed values in cells. Based method if just testing is save file , run code, close then reopen . Unfortunately no quick undo function in vba that i know of

Range.ClearContents Method (Excel)

 

[dilla1988]

Ok cool. thanks for that.

Finally how to write a code which goes through one column and selects ONLY two consecutive empty rows.

Thanks again