Thread: Macro insert cells in first blank column

I am trying to come up with a code that will copy column A entirely, and insert the copied cells into the first blank column. In the columns with information in them, there will always be text in the first cell, so that can be used as the test to find the first blank column, but I'm not sure how to get this done for my macro. Thanks

Hi

Try this

Code:

  Range("A:A").Copy Destination:=Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)

rylo

perfect....before I used this, I had it setup to clear contents of three cells, but it referenced them using the cells that I selected when I recorded my macro. Lets say I wanted to delete the 1st, 3rd and 6th cells in the new column, and leave the first one selected after the macro ran...how would I add this with your code?

thanks so much

Hi

Not sure I follow. If your output goes into column F, then do you want to celar F1, F3 and F6? If so, then you can't use row 1 to determine your next free column.

And what do you mean by

Code:

and leave the first one selected after the macro ran

rylo

You followed right...I am going to add one column, then immediately type something in the first cell, so I wanted it to be blank. I won't be adding two in a row without typing....so how would I clear F1 F3 and F6 if F was the new column? Thanks

Hi

Try this

Code:

outcol = Cells(1, Columns.Count).End(xlToLeft).Column
  Union(Cells(1, outcol), Cells(3, outcol), Cells(6, outcol)).ClearContents

rylo

That didn't quite work either....

The problems with that code is that no matter what, the first time I run the macro, it thinks that the newest column should be column G. SO, when I have something in column G already, it deletes said cells, when really I wanted it to go to the first empty column. Lets say G is the last column with something in it. The first time I run the macro, it does what I said above. If I immediately run it again, it goes back another column, and erases the three cells in column F, instead of going on to column H like I would like it to.

Also, if possible, I want it to highlight the first cell of the new column after deleting the three cells we talked about before. Thanks for your help, sorry for the frustration.

Hi

Can you put up the code you are using. You should do the code to append the new data, then do the code to clear out the 3 cells. It may also pay to attach your working text file. If column G isn't the first available column, is there something in F1 (say a space, or other non visible character) that is making it think column G is the next output column.

rylo

The code I am using is

Code:

Sub NewColumn()
'
' NewColumn Macro
'
' Keyboard Shortcut: Ctrl+e
'
outcol = Cells(1, Columns.Count).End(xlToLeft).Column
  Union(Cells(1, outcol), Cells(3, outcol), Cells(6, outcol)).ClearContents
End Sub

There is text in F and G columns, and nothing in H...so shouldn't it be looking at cell H1 to see that it is completely blank, and then choose column H as the new column?

Hi

This code is to clear out the 3 relevant cells once the data has been copied across. As it stands, it is doing what it is supposed to do.

I thought you wanted to copy data from column A, paste it in the next available column, then clear out 3 cells 1,3, 6. So where do you do the copy / paste before running this code?


rylo

Ok..I'm sorry...I just got to looking at what we had said through this thread, and there was a lot of confusion on my part. I had a code that would copy column A, and paste it, but I didn't know how to make it paste into the first empty column, AND I didn't know how to make it clear out the cells....that might explain the weird behavior of only having the clearing code in there and running it....it works right, but I had deleted my copy/paste code because it didn't work right...sorry

this code finds the first blank cell on the first row and copies column A to the first blank column but i get an error bug on the stop command. maybe this will help if you have any ideas on how to debug it.

Code:

Sub copy()
 Dim i As Integer
 For i = 1 To 500
 If Cells(1, i).Value = "" Then
       Range("A:A").copy Destination:=Cells(1, i)
 Stop
 End If
 Next i
End Sub

This is the closest I have gotten to making this work, which is the same place inwalkedbud got me. I have tried debugging it multiple times but for some reason can't get it past that Stop error. This is so close to what I'm trying to accomplish...anyone know what's wrong here?

Code:

Sub NewColumn()
 Dim i As Integer
 For i = 1 To 20
 If Cells(1, i).Value = "" Then
       Range("B:B").copy Destination:=Cells(1, i)
 Stop
 End If
 Next i
End Sub