VBA/MACRO - If value in column A = 0- then "shift cells down" on the same row for columns C&D

[hewhomustnotbenamed]

Hello Guys,

Would anyone be able to point me in the right direction?

I am loking for a macro to loop down column A

Look for the value 0

Once a 0 is found- I need it to insert (shift cells down) on columns C&D for the same row.

I think this is fairly simple- but my excel skills are rather rusty! I have been looking for a solution all morning- but to no avail!

Any help would be very much apreciated!

Massive Thanks in advance

 

[hewhomustnotbenamed]

I'm tryingto upload an example workbook to help- how can I atach the workbook?

 

[Joe4]

No one can upload/attach files to this site.
However, you can post screen prints using the tools mentioned here: http://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[hewhomustnotbenamed]

Many thanks Joe,

here are the screenshots

Image 1http://i.imgur.com/YebLfrd.png - here we can see a 0 in column A

Image 2 http://i.imgur.com/2TMO8sh.png - this is what I am having to do manually each time I see a 0 in column A- i highlight the same row in column C&D and right click then I press insert and shift cell down

Image 3- imgur: the simple image sharer this is the final result I would like to see

would a macro be able to do this and loop so it run all the way down column A?

many thanks

 

[Joe4]

Your third image looks the same as your first.
Might you have made an error in the image links?
Can you try posting a new link to your third image?

 

[hewhomustnotbenamed]

Doh- Silly me- the third image should be this link
http://i.imgur.com/lHL7sKM.png

Thanks again Joe

 

[Joe4]

Here is a macro that will do that:

Sub MyInsertMacro()


    Dim myLastRow As Long
    Dim myRow As Long
    
    Application.ScreenUpdating = False
    
'   Find last row with data in column A
    myLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows in column A, starting at row 1
    For myRow = 1 To myLastRow
'       Check to see if value in column A is 0
        If Len(Cells(myRow, "A")) = 1 And Cells(myRow, "A") = 0 Then
'           Insert blank columns in C and D
            Range(Cells(myRow, "C"), Cells(myRow, "D")).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        End If
    Next myRow
    
    Application.ScreenUpdating = True
    
End Sub

See if you can follow the logic on how it works. Let me know if you have any questions on how it works.

 

[hewhomustnotbenamed]

Joe4- Thank you so very, very much!

And thank you for explaining each step for me!does?

Can you explain what this part does?= ").End(xlUp).RowPart

 

[Joe4]

myLastRow = Cells(Rows.Count, "A").End(xlUp).Row

First, let's talk a little about the what the first part is.

Cells(rows,column) is another way to reference a range (like Range("A1")).
Rows.Count return the maximum number of rows on a spreadsheet (so it will work for any version of Excel).
The column reference can be alphabetic (like "A") or numeric (like 1).
So what Cells(Rows.Count, "A") says is to go to the very last row in column A.

What .End(xlUp) does is it says to go up from that point until you come to the first cell with data in it, and stop.
So we are starting from the very bottom of column A, and going up until we find data. This is our last cell in column A with data.
(This is the same thing if you were to use CTRL-Up Arrow in Excel.)
The .Row part simply returns the row number of that cell.

So, in a nutshell, that line of code is simply finding the last row in column A containing data.

Note that things like the .End(xlUp) code and the .Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove line of code can be obtained by using the Macro Recorder. This is a very useful tool for VBA programmers (especially new ones!). If you turn on the Macro Recorder, and record yourself performing certain actions, it will record these steps. So you can view it and see what the VBA code for those actions looks like. The code often needs to be cleaned up a little, but it gives you some building blocks you might need for your code (and you don't need to remember all the arguments certain functions have).