Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Data From Above
Hi, wonder if someone can help please.
I have a great bit of coding below from Norman Jones which deletes contents of cells and copies and pastes the data from above rows. I've tried to get hold of him again to see if he could help in tweaking this code perhaps he's had enough of my questions, don't blame him. Anyway, rather than the code looking at the whole page I need a specific range. Norman gave me this amendment 'Set myRange = Activesheet.Range("A2:A10")', but unfortuantely this selects the range specifically looking at rows. I basically need the coding to pick the range columns A to I only, but for it to continue looking down the page copying the data as in the original coding. Can someone help please. Kind regards Chris Public Sub FindCopy() Dim cl As Range Dim myRange As Range Set myRange = ActiveSheet.Range("A2:A" & _ Cells(Rows.Count, "A").End(xlUp).Row) For Each cl In myRange With cl If Len(Trim(.Value)) = 0 Then .Offset(-1, 0).EntireRow.Copy _ Destination:=.Item(1) End If End With Next cl End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Data From Above
Set myRange = ActiveSheet.Range("A2:A" & _
Cells(Rows.Count, "A").End(xlUp).Row).Resize(,9) would extend it out to column I. -- Regards, Tom Ogilvy "ir26121973" wrote: Hi, wonder if someone can help please. I have a great bit of coding below from Norman Jones which deletes contents of cells and copies and pastes the data from above rows. I've tried to get hold of him again to see if he could help in tweaking this code perhaps he's had enough of my questions, don't blame him. Anyway, rather than the code looking at the whole page I need a specific range. Norman gave me this amendment 'Set myRange = Activesheet.Range("A2:A10")', but unfortuantely this selects the range specifically looking at rows. I basically need the coding to pick the range columns A to I only, but for it to continue looking down the page copying the data as in the original coding. Can someone help please. Kind regards Chris Public Sub FindCopy() Dim cl As Range Dim myRange As Range Set myRange = ActiveSheet.Range("A2:A" & _ Cells(Rows.Count, "A").End(xlUp).Row) For Each cl In myRange With cl If Len(Trim(.Value)) = 0 Then .Offset(-1, 0).EntireRow.Copy _ Destination:=.Item(1) End If End With Next cl End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Data From Above
Tom,
Thanks for this. I've tried the code, but unfortunately it still changes the columns out of the range A:I. Any ideas please ? Kind regards Chris "Tom Ogilvy" wrote: Set myRange = ActiveSheet.Range("A2:A" & _ Cells(Rows.Count, "A").End(xlUp).Row).Resize(,9) would extend it out to column I. -- Regards, Tom Ogilvy "ir26121973" wrote: Hi, wonder if someone can help please. I have a great bit of coding below from Norman Jones which deletes contents of cells and copies and pastes the data from above rows. I've tried to get hold of him again to see if he could help in tweaking this code perhaps he's had enough of my questions, don't blame him. Anyway, rather than the code looking at the whole page I need a specific range. Norman gave me this amendment 'Set myRange = Activesheet.Range("A2:A10")', but unfortuantely this selects the range specifically looking at rows. I basically need the coding to pick the range columns A to I only, but for it to continue looking down the page copying the data as in the original coding. Can someone help please. Kind regards Chris Public Sub FindCopy() Dim cl As Range Dim myRange As Range Set myRange = ActiveSheet.Range("A2:A" & _ Cells(Rows.Count, "A").End(xlUp).Row) For Each cl In myRange With cl If Len(Trim(.Value)) = 0 Then .Offset(-1, 0).EntireRow.Copy _ Destination:=.Item(1) End If End With Next cl End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Data From Above
Well, that is true, but you said you wanted it to look at. It was looking
only at column A and I expanded that to I. Now it appears what you want is for it to continue looking only in column A, but to act only out to column I. the adjustment would then be Public Sub FindCopy() Dim cl As Range Dim myRange As Range Set myRange = ActiveSheet.Range("A2:A" & _ Cells(Rows.Count, "A").End(xlUp).Row) For Each cl In myRange With cl If Len(Trim(.Value)) = 0 Then .Offset(-1, 0).Resize(1,9).Copy _ Destination:=.Item(1) End If End With Next cl End Sub -- Regards, Tom Ogilvy "ir26121973" wrote: Tom, Thanks for this. I've tried the code, but unfortunately it still changes the columns out of the range A:I. Any ideas please ? Kind regards Chris "Tom Ogilvy" wrote: Set myRange = ActiveSheet.Range("A2:A" & _ Cells(Rows.Count, "A").End(xlUp).Row).Resize(,9) would extend it out to column I. -- Regards, Tom Ogilvy "ir26121973" wrote: Hi, wonder if someone can help please. I have a great bit of coding below from Norman Jones which deletes contents of cells and copies and pastes the data from above rows. I've tried to get hold of him again to see if he could help in tweaking this code perhaps he's had enough of my questions, don't blame him. Anyway, rather than the code looking at the whole page I need a specific range. Norman gave me this amendment 'Set myRange = Activesheet.Range("A2:A10")', but unfortuantely this selects the range specifically looking at rows. I basically need the coding to pick the range columns A to I only, but for it to continue looking down the page copying the data as in the original coding. Can someone help please. Kind regards Chris Public Sub FindCopy() Dim cl As Range Dim myRange As Range Set myRange = ActiveSheet.Range("A2:A" & _ Cells(Rows.Count, "A").End(xlUp).Row) For Each cl In myRange With cl If Len(Trim(.Value)) = 0 Then .Offset(-1, 0).EntireRow.Copy _ Destination:=.Item(1) End If End With Next cl End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Data From Above
Tom,
Thanks for this. Please excuse my 'Muppetry', very new to VB, learning as I go along. Many thanks for your patience. All the best Chris "Tom Ogilvy" wrote: Well, that is true, but you said you wanted it to look at. It was looking only at column A and I expanded that to I. Now it appears what you want is for it to continue looking only in column A, but to act only out to column I. the adjustment would then be Public Sub FindCopy() Dim cl As Range Dim myRange As Range Set myRange = ActiveSheet.Range("A2:A" & _ Cells(Rows.Count, "A").End(xlUp).Row) For Each cl In myRange With cl If Len(Trim(.Value)) = 0 Then .Offset(-1, 0).Resize(1,9).Copy _ Destination:=.Item(1) End If End With Next cl End Sub -- Regards, Tom Ogilvy "ir26121973" wrote: Tom, Thanks for this. I've tried the code, but unfortunately it still changes the columns out of the range A:I. Any ideas please ? Kind regards Chris "Tom Ogilvy" wrote: Set myRange = ActiveSheet.Range("A2:A" & _ Cells(Rows.Count, "A").End(xlUp).Row).Resize(,9) would extend it out to column I. -- Regards, Tom Ogilvy "ir26121973" wrote: Hi, wonder if someone can help please. I have a great bit of coding below from Norman Jones which deletes contents of cells and copies and pastes the data from above rows. I've tried to get hold of him again to see if he could help in tweaking this code perhaps he's had enough of my questions, don't blame him. Anyway, rather than the code looking at the whole page I need a specific range. Norman gave me this amendment 'Set myRange = Activesheet.Range("A2:A10")', but unfortuantely this selects the range specifically looking at rows. I basically need the coding to pick the range columns A to I only, but for it to continue looking down the page copying the data as in the original coding. Can someone help please. Kind regards Chris Public Sub FindCopy() Dim cl As Range Dim myRange As Range Set myRange = ActiveSheet.Range("A2:A" & _ Cells(Rows.Count, "A").End(xlUp).Row) For Each cl In myRange With cl If Len(Trim(.Value)) = 0 Then .Offset(-1, 0).EntireRow.Copy _ Destination:=.Item(1) End If End With Next cl End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
filted data, copy and paste a col. puts data in wrong row how fix | New Users to Excel | |||
Retrieve multiple data rows data from a very long list and copy t | Excel Discussion (Misc queries) | |||
How do I copy data from main frame computer and keep data in cell | Excel Worksheet Functions | |||
Copy old Data from web query while keeping previous days data | Excel Worksheet Functions | |||
Copy data into cells until changes trigger new copy | Excel Programming |