VBA help with filling empty rows with data

dilla1988

New Member
Joined
Sep 4, 2014
Messages
22
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

tyger0951

Board Regular
Joined
Aug 9, 2014
Messages
103
This should get you started. Post some of your own code and any errors you run into for further help :)

Code:
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 :)
 
Last edited:

dilla1988

New Member
Joined
Sep 4, 2014
Messages
22
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 :)

Code:
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

Board Regular
Joined
Aug 9, 2014
Messages
103
Give this a shot

Code:
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
 
Last edited:

tyger0951

Board Regular
Joined
Aug 9, 2014
Messages
103

ADVERTISEMENT

If issues may want to move to


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



Give this a shot

Code:
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

New Member
Joined
Sep 4, 2014
Messages
22
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


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

tyger0951

Board Regular
Joined
Aug 9, 2014
Messages
103

ADVERTISEMENT

Code:
Cell.offset(0,2).pastespecial

Because
Code:
.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

New Member
Joined
Sep 4, 2014
Messages
22
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

Board Regular
Joined
Aug 9, 2014
Messages
103
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

New Member
Joined
Sep 4, 2014
Messages
22
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,800
Messages
5,638,430
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top