Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Empty Column and paste cell values
I have a question that I hope someone can help
I want to write a Macro that finds the first empy column in Range (A1:IU),cells A1:F1=" ", in worksheet then paste the current values in Worksheet1,cells (A1:F1) to Worksheet2, (a1:f1) thanks for the hel mike |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Empty Column and paste cell values
worksheets("Sheet2").Range("A1:F1").Value = _
worksheets("Sheet1").Range("A1:F1").Value Dim rng as Range for each cell in Range("A1:IV1") if application.countA(cell.EntireColumn) = 0 then set rng = cell.Entirecolumn Exit for End if Next if not rng is nothing then rng.Select -- Regards, Tom Ogilvy Mike wrote in message ... I have a question that I hope someone can help. I want to write a Macro that finds the first empy column in Range (A1:IU),cells A1:F1=" ", in worksheet2 then paste the current values in Worksheet1,cells (A1:F1) to Worksheet2, (a1:f1). thanks for the help mike |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Empty Column and paste cell values
Tom
Thanks for responding but I am still having trouble with the program I will rewrite the question and maybe you can see what I am not understanding. I want to write a Macro that finds the first empy column in Range (A1:IU),cells A1:F1=" ", in worksheet2** the whole column does not have to be empty just the first 6 rows. then paste the current values in Worksheet1,cells (A1:F1) to Worksheet2, (a1:f1). ( having trouble copying the cells in worksheet 1(A1:f1) to the empty cells (a1:f1) IN worksheet 2 I am using worksheet 2 as a database worksheet, so it is important to save the data in worsheet 1. Also, how do the below statements fit into the subrouutine worksheets("Sheet2").Range("A1:F1").Value = _ worksheets("Sheet1").Range("A1:F1").Value I hope you can figure out what I need to do. thanks for your help mike ----- Tom Ogilvy wrote: ----- worksheets("Sheet2").Range("A1:F1").Value = _ worksheets("Sheet1").Range("A1:F1").Value Dim rng as Range for each cell in Range("A1:IV1") if application.countA(cell.EntireColumn) = 0 then set rng = cell.Entirecolumn Exit for End if Next if not rng is nothing then rng.Select -- Regards, Tom Ogilvy Mike wrote in message ... I have a question that I hope someone can help. I want to write a Macro that finds the first empy column in Range (A1:IU),cells A1:F1=" ", in worksheet2 then paste the current values in Worksheet1,cells (A1:F1) to Worksheet2, (a1:f1). thanks for the help mike |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Empty Column and paste cell values
then paste the current values in Worksheet1,cells (A1:F1) to
Worksheet2, (a1:f1). ( having trouble copying the cells in worksheet 1(A1:f1) to the empty cells (a1:f1) IN worksheet 2 I am using worksheet 2 as a database worksheet, so it is important to save the data in worsheet 1. Also, how do the below statements fit into the subrouutine worksheets("Sheet2").Range("A1:F1").Value = _ worksheets("Sheet1").Range("A1:F1").Value the above code does what you state. I hope you can figure out what I need to do. I tried - but most of what you write sounds like blither speak. for instance: I want to write a Macro that finds the first empy column in Range (A1:IU),cells A1:F1=" ", A1:IU - what is that supposed to mean? IU is a column Reference, A1 is a cell reference. I assumed you wanted the the last column which is IV and the first row A1:IV1 What is [,cells A1:F1=" "] supposed to mean in that context. Assumed A1:F1 are empty cells - but what is the significance. If you want the first empty column after F1, then why say A1:IU, say G1:IV1 or whatever you mean. for each cell in Range("G1:IV1") if application.countA(cell.Resize(6,1)) = 0 then Cell.Resize(6,1).Select exit for end if Next Note that a cell that has a space in it is not empty. -- Regards, Tom Ogilvy "Mike" wrote in message ... Tom Thanks for responding but I am still having trouble with the program I will rewrite the question and maybe you can see what I am not understanding. I want to write a Macro that finds the first empy column in Range (A1:IU),cells A1:F1=" ", in worksheet2** the whole column does not have to be empty just the first 6 rows. then paste the current values in Worksheet1,cells (A1:F1) to Worksheet2, (a1:f1). ( having trouble copying the cells in worksheet 1(A1:f1) to the empty cells (a1:f1) IN worksheet 2 I am using worksheet 2 as a database worksheet, so it is important to save the data in worsheet 1. Also, how do the below statements fit into the subrouutine worksheets("Sheet2").Range("A1:F1").Value = _ worksheets("Sheet1").Range("A1:F1").Value I hope you can figure out what I need to do. thanks for your help mike ----- Tom Ogilvy wrote: ----- worksheets("Sheet2").Range("A1:F1").Value = _ worksheets("Sheet1").Range("A1:F1").Value Dim rng as Range for each cell in Range("A1:IV1") if application.countA(cell.EntireColumn) = 0 then set rng = cell.Entirecolumn Exit for End if Next if not rng is nothing then rng.Select -- Regards, Tom Ogilvy Mike wrote in message ... I have a question that I hope someone can help. I want to write a Macro that finds the first empy column in Range (A1:IU),cells A1:F1=" ", in worksheet2 then paste the current values in Worksheet1,cells (A1:F1) to Worksheet2, (a1:f1). thanks for the help mike |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Empty Column and paste cell values
To
Thanks for the help but I am still having problems. I going to give an example and then maybe you ca see where I am going wrong. Example, Worksheet 1, cell A1= bob, cell A2 = mike, cell A3= jim I want the macro to goto Worksheet 2 and find the first column where the cells a1 through a3 are empty Then I want the macro to copy the contents of Worksheet1, a1:a3 into the empty cells (A1:A3) in the selected column i Worksheet 2. I was also having trouble understanding the copy commands (copying Worsheet 1 A1:a3 into the selected empty cells in Worksheet 2) when the code that you sent to me finds the firs empty column and exits the for-next loop I appreciate your helping through this Macro. Once I am able to get this Macro working, I pla to use it on many other applications Mike |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Empty Column and paste cell values
Much clearer:
Sub copyData() Dim rng As Range, cell As Range On Error Resume Next With Worksheets("Sheet2") Set rng = .Rows(1).SpecialCells(xlBlanks) End With On Error GoTo 0 If rng Is Nothing Then Exit Sub For Each cell In rng If Application.CountA(cell.Resize(3, 1)) = 0 Then Worksheets("sheet1").Range("A1:A3").Copy _ Destination:=cell Exit For End If Next End Sub Worked for me based on your description. -- Regards, Tom Ogilvy Mike wrote in message ... Tom Thanks for the help but I am still having problems. I going to give an example and then maybe you can see where I am going wrong. Example, Worksheet 1, cell A1= bob, cell A2 = mike, cell A3= jim. I want the macro to goto Worksheet 2 and find the first column where the cells a1 through a3 are empty. Then I want the macro to copy the contents of Worksheet1, a1:a3 into the empty cells (A1:A3) in the selected column in Worksheet 2. I was also having trouble understanding the copy commands (copying Worsheet 1 A1:a3 into the selected empty cells in Worksheet 2) when the code that you sent to me finds the first empty column and exits the for-next loop. I appreciate your helping through this Macro. Once I am able to get this Macro working, I plan to use it on many other applications. Mike |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Empty Column and paste cell values
Tom:
Took a Christmas break. Hope yours was a happy and merry one! Anyway, thanks for the reply, but still not able to get the macro to work. I am using Excell 2000. I made a macro and copied your instructions into the macro as you sent them to me. Tried running the macro from the macro - run macro selection and also from a macro button assigned to the macro. Both ways produced no results at all. No copying of worksheet 1, a1:a3 to the the worksheet 2. Not sure what I am doing wrong but I have appreciated all your help. Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find first empty cell in column | Excel Discussion (Misc queries) | |||
Find first empty cell in column | Excel Discussion (Misc queries) | |||
How to: Find first empty cell in column | Excel Worksheet Functions | |||
find first empty cell in column and start transpose next row in that cell | Excel Discussion (Misc queries) | |||
Find a empty cell in next column | Excel Discussion (Misc queries) |