| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop?
can you help me
i'm trying to create a macro that will loop through all the data in a user specified column/row and return on a new sheet all non-blank data from that selected colum or row with the relevant row/column headers as appear on original sheet. I'm a bit new to this sorry! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop?
With Worksheets("Sheet2")
Worksheets("Sheet1")Column(1).Copy .Range("A1") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = LastRow To 2 Step -1 If .Cells(i, "A").Value = "" Then .Rows(i).Delete End If Next i End With -- __________________________________ HTH Bob "scaredycat" wrote in message ... can you help me i'm trying to create a macro that will loop through all the data in a user specified column/row and return on a new sheet all non-blank data from that selected colum or row with the relevant row/column headers as appear on original sheet. I'm a bit new to this sorry! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop?
Thanks Bob but excel has a prblem with this it has a message that says "sub
or function not defined" for the word 'Column' in this line Worksheets("Sheet1")Column(1).Copy .Range("A1") I'm probably being really basic but how do I fix this? many thanks "Bob Phillips" wrote: With Worksheets("Sheet2") Worksheets("Sheet1")Column(1).Copy .Range("A1") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = LastRow To 2 Step -1 If .Cells(i, "A").Value = "" Then .Rows(i).Delete End If Next i End With -- __________________________________ HTH Bob "scaredycat" wrote in message ... can you help me i'm trying to create a macro that will loop through all the data in a user specified column/row and return on a new sheet all non-blank data from that selected colum or row with the relevant row/column headers as appear on original sheet. I'm a bit new to this sorry! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop?
typo.......
Worksheets("Sheet1")Column(1).Copy .Range("A1") should be Worksheets("Sheet1").Column(1).Copy.Range("A1") :) susan On Jun 24, 3:02*pm, "Bob Phillips" wrote: * * With Worksheets("Sheet2") * * * * Worksheets("Sheet1")Column(1).Copy .Range("A1") * * * * LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row * * * * For i = LastRow To 2 Step -1 * * * * * * If .Cells(i, "A").Value = "" Then * * * * * * * * .Rows(i).Delete * * * * * * End If * * * * Next i * * End With -- __________________________________ HTH Bob "scaredycat" wrote in message ... can you help me i'm trying to create a macro that will loop through all the data in a user specified column/row and return on a new sheet all non-blank data from that selected colum or row with the relevant row/column headers as appear on original sheet. I'm a bit new to this sorry!- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop?
Typo on my part, try
Worksheets("Sheet1").Columns(1).Copy .Range("A1") -- __________________________________ HTH Bob "scaredycat" wrote in message ... Thanks Bob but excel has a prblem with this it has a message that says "sub or function not defined" for the word 'Column' in this line Worksheets("Sheet1")Column(1).Copy .Range("A1") I'm probably being really basic but how do I fix this? many thanks "Bob Phillips" wrote: With Worksheets("Sheet2") Worksheets("Sheet1")Column(1).Copy .Range("A1") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = LastRow To 2 Step -1 If .Cells(i, "A").Value = "" Then .Rows(i).Delete End If Next i End With -- __________________________________ HTH Bob "scaredycat" wrote in message ... can you help me i'm trying to create a macro that will loop through all the data in a user specified column/row and return on a new sheet all non-blank data from that selected colum or row with the relevant row/column headers as appear on original sheet. I'm a bit new to this sorry! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop?
You missed the other one
Worksheets("Sheet1").Columns(1).Copy.Range("A1") -- __________________________________ HTH Bob "Susan" wrote in message ... typo....... Worksheets("Sheet1")Column(1).Copy .Range("A1") should be Worksheets("Sheet1").Column(1).Copy.Range("A1") :) susan On Jun 24, 3:02 pm, "Bob Phillips" wrote: With Worksheets("Sheet2") Worksheets("Sheet1")Column(1).Copy .Range("A1") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = LastRow To 2 Step -1 If .Cells(i, "A").Value = "" Then .Rows(i).Delete End If Next i End With -- __________________________________ HTH Bob "scaredycat" wrote in message ... can you help me i'm trying to create a macro that will loop through all the data in a user specified column/row and return on a new sheet all non-blank data from that selected colum or row with the relevant row/column headers as appear on original sheet. I'm a bit new to this sorry!- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop?
yep, i got the & the space, but missed the s on the columns.......
LOL susan On Jun 24, 3:25*pm, "Bob Phillips" wrote: You missed the other one Worksheets("Sheet1").Columns(1).Copy.Range("A1") -- __________________________________ HTH Bob "Susan" wrote in message ... typo....... Worksheets("Sheet1")Column(1).Copy .Range("A1") should be Worksheets("Sheet1").Column(1).Copy.Range("A1") :) susan On Jun 24, 3:02 pm, "Bob Phillips" wrote: With Worksheets("Sheet2") Worksheets("Sheet1")Column(1).Copy .Range("A1") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = LastRow To 2 Step -1 If .Cells(i, "A").Value = "" Then .Rows(i).Delete End If Next i End With -- __________________________________ HTH Bob "scaredycat" wrote in message ... can you help me i'm trying to create a macro that will loop through all the data in a user specified column/row and return on a new sheet all non-blank data from that selected colum or row with the relevant row/column headers as appear on original sheet. I'm a bit new to this sorry!- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop?
Sorry guys... it still doesn't like that line i've now got a message saying
"runtime error 424 object required" what does that mean? "Bob Phillips" wrote: You missed the other one Worksheets("Sheet1").Columns(1).Copy.Range("A1") -- __________________________________ HTH Bob "Susan" wrote in message ... typo....... Worksheets("Sheet1")Column(1).Copy .Range("A1") should be Worksheets("Sheet1").Column(1).Copy.Range("A1") :) susan On Jun 24, 3:02 pm, "Bob Phillips" wrote: With Worksheets("Sheet2") Worksheets("Sheet1")Column(1).Copy .Range("A1") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = LastRow To 2 Step -1 If .Cells(i, "A").Value = "" Then .Rows(i).Delete End If Next i End With -- __________________________________ HTH Bob "scaredycat" wrote in message ... can you help me i'm trying to create a macro that will loop through all the data in a user specified column/row and return on a new sheet all non-blank data from that selected colum or row with the relevant row/column headers as appear on original sheet. I'm a bit new to this sorry!- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop?
Worksheets("Sheet1").Columns(1).Copy _
destination:=.Range("A1") Or Worksheets("Sheet1").Columns(1).Copy .Range("A1") '<--note the spaces! scaredycat wrote: Sorry guys... it still doesn't like that line i've now got a message saying "runtime error 424 object required" what does that mean? "Bob Phillips" wrote: You missed the other one Worksheets("Sheet1").Columns(1).Copy.Range("A1") -- __________________________________ HTH Bob "Susan" wrote in message ... typo....... Worksheets("Sheet1")Column(1).Copy .Range("A1") should be Worksheets("Sheet1").Column(1).Copy.Range("A1") :) susan On Jun 24, 3:02 pm, "Bob Phillips" wrote: With Worksheets("Sheet2") Worksheets("Sheet1")Column(1).Copy .Range("A1") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = LastRow To 2 Step -1 If .Cells(i, "A").Value = "" Then .Rows(i).Delete End If Next i End With -- __________________________________ HTH Bob "scaredycat" wrote in message ... can you help me i'm trying to create a macro that will loop through all the data in a user specified column/row and return on a new sheet all non-blank data from that selected colum or row with the relevant row/column headers as appear on original sheet. I'm a bit new to this sorry!- Hide quoted text - - Show quoted text - -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop?
Thanks guys for all your help, but I still can't get it to work, what object
do I need to define....i'm sorry to bother you further but thank you so much for your help so far Thanks :) "Susan" wrote: yep, i got the & the space, but missed the s on the columns....... LOL susan On Jun 24, 3:25 pm, "Bob Phillips" wrote: You missed the other one Worksheets("Sheet1").Columns(1).Copy.Range("A1") -- __________________________________ HTH Bob "Susan" wrote in message ... typo....... Worksheets("Sheet1")Column(1).Copy .Range("A1") should be Worksheets("Sheet1").Column(1).Copy.Range("A1") :) susan On Jun 24, 3:02 pm, "Bob Phillips" wrote: With Worksheets("Sheet2") Worksheets("Sheet1")Column(1).Copy .Range("A1") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = LastRow To 2 Step -1 If .Cells(i, "A").Value = "" Then .Rows(i).Delete End If Next i End With -- __________________________________ HTH Bob "scaredycat" wrote in message ... can you help me i'm trying to create a macro that will loop through all the data in a user specified column/row and return on a new sheet all non-blank data from that selected colum or row with the relevant row/column headers as appear on original sheet. I'm a bit new to this sorry!- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop?
Sorry I just got the answer to that last bit and the good news is it works...yay:), the only thing is though it only specifies the column headers and row names it doesn't filter the data in those columns/rows by non-blank values and remove the unnecessary ones. I'm not very good at explaining things sorry, so for examples sake if a user clicks on a row name I want to copy to a seperate sheet all the non-blank columns and values for that row, and likewise for selecting a column i want to filter that column by all the non-blank values and copy those values with the row names (so that the data is meaningful) to a seperate sheet. Does this make any sense, i'm sorry to take up your time. Yours gratefully scaredycat :) "scaredycat" wrote: Thanks guys for all your help, but I still can't get it to work, what object do I need to define....i'm sorry to bother you further but thank you so much for your help so far Thanks :) "Susan" wrote: yep, i got the & the space, but missed the s on the columns....... LOL susan On Jun 24, 3:25 pm, "Bob Phillips" wrote: You missed the other one Worksheets("Sheet1").Columns(1).Copy.Range("A1") -- __________________________________ HTH Bob "Susan" wrote in message ... typo....... Worksheets("Sheet1")Column(1).Copy .Range("A1") should be Worksheets("Sheet1").Column(1).Copy.Range("A1") :) susan On Jun 24, 3:02 pm, "Bob Phillips" wrote: With Worksheets("Sheet2") Worksheets("Sheet1")Column(1).Copy .Range("A1") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = LastRow To 2 Step -1 If .Cells(i, "A").Value = "" Then .Rows(i).Delete End If Next i End With -- __________________________________ HTH Bob "scaredycat" wrote in message ... can you help me i'm trying to create a macro that will loop through all the data in a user specified column/row and return on a new sheet all non-blank data from that selected colum or row with the relevant row/column headers as appear on original sheet. I'm a bit new to this sorry!- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop?
Isn't that just what it does, copy column A, without the blanks?
-- __________________________________ HTH Bob "scaredycat" wrote in message ... Sorry I just got the answer to that last bit and the good news is it works...yay:), the only thing is though it only specifies the column headers and row names it doesn't filter the data in those columns/rows by non-blank values and remove the unnecessary ones. I'm not very good at explaining things sorry, so for examples sake if a user clicks on a row name I want to copy to a seperate sheet all the non-blank columns and values for that row, and likewise for selecting a column i want to filter that column by all the non-blank values and copy those values with the row names (so that the data is meaningful) to a seperate sheet. Does this make any sense, i'm sorry to take up your time. Yours gratefully scaredycat :) "scaredycat" wrote: Thanks guys for all your help, but I still can't get it to work, what object do I need to define....i'm sorry to bother you further but thank you so much for your help so far Thanks :) "Susan" wrote: yep, i got the & the space, but missed the s on the columns....... LOL susan On Jun 24, 3:25 pm, "Bob Phillips" wrote: You missed the other one Worksheets("Sheet1").Columns(1).Copy.Range("A1") -- __________________________________ HTH Bob "Susan" wrote in message ... typo....... Worksheets("Sheet1")Column(1).Copy .Range("A1") should be Worksheets("Sheet1").Column(1).Copy.Range("A1") :) susan On Jun 24, 3:02 pm, "Bob Phillips" wrote: With Worksheets("Sheet2") Worksheets("Sheet1")Column(1).Copy .Range("A1") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = LastRow To 2 Step -1 If .Cells(i, "A").Value = "" Then .Rows(i).Delete End If Next i End With -- __________________________________ HTH Bob "scaredycat" wrote in message ... can you help me i'm trying to create a macro that will loop through all the data in a user specified column/row and return on a new sheet all non-blank data from that selected colum or row with the relevant row/column headers as appear on original sheet. I'm a bit new to this sorry!- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop?
It does that you're right but what I want it to do is as follows (sorry i'm
not so good with the words!) Column A contains names of students, The other column headers are exams, the data in the table are the dates that the students passed particular exams, what i want to do is select an exam e.g. exam 1 and produce on a different sheet the names of the students that have taken that exam and the respective dates from that column...do you see? Also I want to be able to select a students name and produce on a different sheet the names of the exams that they have passed and the dates. but I don't want to show the column headers (e.g. exam names for the exams not yet passed) Is that clearer, i'm sorry I'm rubbish at explaining but i'm learning a lot :) thank you "Bob Phillips" wrote: Isn't that just what it does, copy column A, without the blanks? -- __________________________________ HTH Bob "scaredycat" wrote in message ... Sorry I just got the answer to that last bit and the good news is it works...yay:), the only thing is though it only specifies the column headers and row names it doesn't filter the data in those columns/rows by non-blank values and remove the unnecessary ones. I'm not very good at explaining things sorry, so for examples sake if a user clicks on a row name I want to copy to a seperate sheet all the non-blank columns and values for that row, and likewise for selecting a column i want to filter that column by all the non-blank values and copy those values with the row names (so that the data is meaningful) to a seperate sheet. Does this make any sense, i'm sorry to take up your time. Yours gratefully scaredycat :) "scaredycat" wrote: Thanks guys for all your help, but I still can't get it to work, what object do I need to define....i'm sorry to bother you further but thank you so much for your help so far Thanks :) "Susan" wrote: yep, i got the & the space, but missed the s on the columns....... LOL susan On Jun 24, 3:25 pm, "Bob Phillips" wrote: You missed the other one Worksheets("Sheet1").Columns(1).Copy.Range("A1") -- __________________________________ HTH Bob "Susan" wrote in message ... typo....... Worksheets("Sheet1")Column(1).Copy .Range("A1") should be Worksheets("Sheet1").Column(1).Copy.Range("A1") :) susan On Jun 24, 3:02 pm, "Bob Phillips" wrote: With Worksheets("Sheet2") Worksheets("Sheet1")Column(1).Copy .Range("A1") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = LastRow To 2 Step -1 If .Cells(i, "A").Value = "" Then .Rows(i).Delete End If Next i End With -- __________________________________ HTH Bob "scaredycat" wrote in message ... can you help me i'm trying to create a macro that will loop through all the data in a user specified column/row and return on a new sheet all non-blank data from that selected colum or row with the relevant row/column headers as appear on original sheet. I'm a bit new to this sorry!- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| returning back to loop check condition without completing the loop | Excel Programming | |||
| Loop to Filter, Name Sheets. If Blank, Exit Loop | Excel Programming | |||
| Naming Worksheets - Loop within a loop issue | Excel Programming | |||
| (Complex) Loop within loop to create worksheets | Excel Programming | |||
| Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
Linear Mode
