Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to insert column between q.
I have 500+ question in a row,how can I insert column in between every
questions but there are some questions already inserted with column,is there a method to save time so that each q. will have only 1 blank column in between it? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to insert column between q.
Given that Excel only has 256 columns, how can you have 500 questions in a row?
So do you mean columns? "cy" wrote: I have 500+ question in a row,how can I insert column in between every questions but there are some questions already inserted with column,is there a method to save time so that each q. will have only 1 blank column in between it? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to insert column between q.
cy
There are only 256 columns in Excel so 500+ is out of the question and inserting even more columns is in the same category. Perhaps re-think your question and re-post when more clear what you want to do. Rows or Columns..........which is it? Rows are 1,2,3,4,5 etc and Columns are A,B,C,D etc. Gord Dibben MS Excel MVP On Sun, 30 Apr 2006 11:29:02 -0700, cy wrote: I have 500+ question in a row,how can I insert column in between every questions but there are some questions already inserted with column,is there a method to save time so that each q. will have only 1 blank column in between it? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to insert column between q.
You must mean rows instead of columns.
Yes, VBA can do that, but Excel needs to know how to identify a line as being the beginning of a new question. Pretend that you are explaining how to do this to someone who doesn't know your data. What would you say to him? HTH Otto "cy" wrote in message ... I have 500+ question in a row,how can I insert column in between every questions but there are some questions already inserted with column,is there a method to save time so that each q. will have only 1 blank column in between it? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to insert column between q.
you don't know what i mean?
let say i have 5q. in a row that is A1(q.1),A2(q.2),A3(q.3),A4(q.4),A5(q.5) how can i insert a column next to each q. so that the seqence now is A1(q.1),A2(blank),A3(q.2)........ but some blank columns have already inserted,so how to write that VBA? "Otto Moehrbach" 來函: You must mean rows instead of columns. Yes, VBA can do that, but Excel needs to know how to identify a line as being the beginning of a new question. Pretend that you are explaining how to do this to someone who doesn't know your data. What would you say to him? HTH Otto "cy" wrote in message ... I have 500+ question in a row,how can I insert column in between every questions but there are some questions already inserted with column,is there a method to save time so that each q. will have only 1 blank column in between it? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to insert column between q.
If you didn't call Row as Column you would be more easily understood For what you require, see Dave Peterson's answer in http://www.excelforum.com/showthread.php?t=537047 -- cy Wrote: you don't know what i mean? let say i have 5q. in a row that is A1(q.1),A2(q.2),A3(q.3),A4(q.4),A5(q.5) how can i insert a column next to each q. so that the seqence now is A1(q.1),A2(blank),A3(q.2)........ but some blank columns have already inserted,so how to write that VBA? "Otto Moehrbach" 來函: You must mean rows instead of columns. Yes, VBA can do that, but Excel needs to know how to identify a line as being the beginning of a new question. Pretend that you are explaining how to do this to someone who doesn't know your data. What would you say to him? HTH Otto "cy" wrote in message ... I have 500+ question in a row,how can I insert column in between every questions but there are some questions already inserted with column,is there a method to save time so that each q. will have only 1 blank column in between it? -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=537608 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to insert column between q.
You are correct,Bryan i mix up column and row wrongly,and the answer is
partly correct only ..It is because like i said before,there are some blank rows already inserted,after running the marco ,there will be 3 blank together,so is there possible to rewrite the macro so that it will not adding a blank row before and after the blank row that already exist? "Bryan Hessey" 來函: If you didn't call Row as Column you would be more easily understood For what you require, see Dave Peterson's answer in http://www.excelforum.com/showthread.php?t=537047 -- cy Wrote: you don't know what i mean? let say i have 5q. in a row that is A1(q.1),A2(q.2),A3(q.3),A4(q.4),A5(q.5) how can i insert a column next to each q. so that the seqence now is A1(q.1),A2(blank),A3(q.2)........ but some blank columns have already inserted,so how to write that VBA? "Otto Moehrbach" ä¾€*Ã¥€¡Â½Ã¯Â¼Å¡ You must mean rows instead of columns. Yes, VBA can do that, but Excel needs to know how to identify a line as being the beginning of a new question. Pretend that you are explaining how to do this to someone who doesn't know your data. What would you say to him? HTH Otto "cy" wrote in message ... I have 500+ question in a row,how can I insert column in between every questions but there are some questions already inserted with column,is there a method to save time so that each q. will have only 1 blank column in between it? -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=537608 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to insert column between q.
If you have already started to insert blanks, then adjust the number shown as Startrow= from 1 to the first row you need. (in Dave's code) FirstRow = 1 'no headers to FirstRow = 25 'no headers - the number being your first desired row To insert only 1 blank, amend .Rows(iRow).Resize(2).Insert to .Rows(iRow).Resize(1).Insert This should work. --- cy Wrote: You are correct,Bryan i mix up column and row wrongly,and the answer is partly correct only ..It is because like i said before,there are some blank rows already inserted,after running the marco ,there will be 3 blank together,so is there possible to rewrite the macro so that it will not adding a blank row before and after the blank row that already exist? "Bryan Hessey" 來函: If you didn't call Row as Column you would be more easily understood For what you require, see Dave Peterson's answer in http://www.excelforum.com/showthread.php?t=537047 -- cy Wrote: you don't know what i mean? let say i have 5q. in a row that is A1(q.1),A2(q.2),A3(q.3),A4(q.4),A5(q.5) how can i insert a column next to each q. so that the seqence now is A1(q.1),A2(blank),A3(q.2)........ but some blank columns have already inserted,so how to write that VBA? "Otto Moehrbach" ä¾€*Ã¥€¡Â½Ã¯Â¼Å¡ You must mean rows instead of columns. Yes, VBA can do that, but Excel needs to know how to identify a line as being the beginning of a new question. Pretend that you are explaining how to do this to someone who doesn't know your data. What would you say to him? HTH Otto "cy" wrote in message ... I have 500+ question in a row,how can I insert column in between every questions but there are some questions already inserted with column,is there a method to save time so that each q. will have only 1 blank column in between it? -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=537608 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=537608 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to insert column between q.
oh my..............my blanks is distributed randomly,so is there any method
to chg the code to avoid insert blank when matching blanks "Bryan Hessey" 來函: If you have already started to insert blanks, then adjust the number shown as Startrow= from 1 to the first row you need. (in Dave's code) FirstRow = 1 'no headers to FirstRow = 25 'no headers - the number being your first desired row To insert only 1 blank, amend .Rows(iRow).Resize(2).Insert to .Rows(iRow).Resize(1).Insert This should work. --- cy Wrote: You are correct,Bryan i mix up column and row wrongly,and the answer is partly correct only ..It is because like i said before,there are some blank rows already inserted,after running the marco ,there will be 3 blank together,so is there possible to rewrite the macro so that it will not adding a blank row before and after the blank row that already exist? "Bryan Hessey" ä¾€*Ã¥€¡Â½Ã¯Â¼Å¡ If you didn't call Row as Column you would be more easily understood For what you require, see Dave Peterson's answer in http://www.excelforum.com/showthread.php?t=537047 -- cy Wrote: you don't know what i mean? let say i have 5q. in a row that is A1(q.1),A2(q.2),A3(q.3),A4(q.4),A5(q.5) how can i insert a column next to each q. so that the seqence now is A1(q.1),A2(blank),A3(q.2)........ but some blank columns have already inserted,so how to write that VBA? "Otto Moehrbach" ä¾â‚¬*倡½ï¼š You must mean rows instead of columns. Yes, VBA can do that, but Excel needs to know how to identify a line as being the beginning of a new question. Pretend that you are explaining how to do this to someone who doesn't know your data. What would you say to him? HTH Otto "cy" wrote in message ... I have 500+ question in a row,how can I insert column in between every questions but there are some questions already inserted with column,is there a method to save time so that each q. will have only 1 blank column in between it? -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=537608 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=537608 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to insert column between q.
try amending to: For iRow = LastRow To FirstRow + 1 Step -1 If .Cells(iRow - 1, 1).Value = "" Then iRow = iRow - 1 Else .Rows(iRow).Resize(1).Insert End If Next iRow -- cy Wrote: oh my..............my blanks is distributed randomly,so is there any method to chg the code to avoid insert blank when matching blanks "Bryan Hessey" 來函: If you have already started to insert blanks, then adjust the number shown as Startrow= from 1 to the first row you need. (in Dave's code) FirstRow = 1 'no headers to FirstRow = 25 'no headers - the number being your first desired row To insert only 1 blank, amend .Rows(iRow).Resize(2).Insert to .Rows(iRow).Resize(1).Insert This should work. --- cy Wrote: You are correct,Bryan i mix up column and row wrongly,and the answer is partly correct only ..It is because like i said before,there are some blank rows already inserted,after running the marco ,there will be 3 blank together,so is there possible to rewrite the macro so that it will not adding a blank row before and after the blank row that already exist? "Bryan Hessey" ä¾€*Ã¥€¡Â½Ã¯Â¼Å¡ If you didn't call Row as Column you would be more easily understood For what you require, see Dave Peterson's answer in http://www.excelforum.com/showthread.php?t=537047 -- cy Wrote: you don't know what i mean? let say i have 5q. in a row that is A1(q.1),A2(q.2),A3(q.3),A4(q.4),A5(q.5) how can i insert a column next to each q. so that the seqence now is A1(q.1),A2(blank),A3(q.2)........ but some blank columns have already inserted,so how to write that VBA? "Otto Moehrbach" ä¾â‚¬*倡½ï¼š You must mean rows instead of columns. Yes, VBA can do that, but Excel needs to know how to identify a line as being the beginning of a new question. Pretend that you are explaining how to do this to someone who doesn't know your data. What would you say to him? HTH Otto "cy" wrote in message ... I have 500+ question in a row,how can I insert column in between every questions but there are some questions already inserted with column,is there a method to save time so that each q. will have only 1 blank column in between it? -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=537608 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=537608 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=537608 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to insert column between q.
That is awesome,thankyou:D
"Bryan Hessey" 來函: try amending to: For iRow = LastRow To FirstRow + 1 Step -1 If .Cells(iRow - 1, 1).Value = "" Then iRow = iRow - 1 Else .Rows(iRow).Resize(1).Insert End If Next iRow -- cy Wrote: oh my..............my blanks is distributed randomly,so is there any method to chg the code to avoid insert blank when matching blanks "Bryan Hessey" ä¾€*Ã¥€¡Â½Ã¯Â¼Å¡ If you have already started to insert blanks, then adjust the number shown as Startrow= from 1 to the first row you need. (in Dave's code) FirstRow = 1 'no headers to FirstRow = 25 'no headers - the number being your first desired row To insert only 1 blank, amend .Rows(iRow).Resize(2).Insert to .Rows(iRow).Resize(1).Insert This should work. --- cy Wrote: You are correct,Bryan i mix up column and row wrongly,and the answer is partly correct only ..It is because like i said before,there are some blank rows already inserted,after running the marco ,there will be 3 blank together,so is there possible to rewrite the macro so that it will not adding a blank row before and after the blank row that already exist? "Bryan Hessey" ä¾â‚¬*倡½ï¼š If you didn't call Row as Column you would be more easily understood For what you require, see Dave Peterson's answer in http://www.excelforum.com/showthread.php?t=537047 -- cy Wrote: you don't know what i mean? let say i have 5q. in a row that is A1(q.1),A2(q.2),A3(q.3),A4(q.4),A5(q.5) how can i insert a column next to each q. so that the seqence now is A1(q.1),A2(blank),A3(q.2)........ but some blank columns have already inserted,so how to write that VBA? "Otto Moehrbach" äÀšÃ‚¾Ã¢â€šÂ¬*倠¡Ã€šÃ‚½ÃƒÂ¯Ã€šÃ‚¼Ã€¦Ã‚¡ You must mean rows instead of columns. Yes, VBA can do that, but Excel needs to know how to identify a line as being the beginning of a new question. Pretend that you are explaining how to do this to someone who doesn't know your data. What would you say to him? HTH Otto "cy" wrote in message ... I have 500+ question in a row,how can I insert column in between every questions but there are some questions already inserted with column,is there a method to save time so that each q. will have only 1 blank column in between it? -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=537608 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=537608 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=537608 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to insert column between q.
No Cy, I don't know what you mean, that's why I asked the question.
Apparently, your data has one question per row. You failed to mention that. You could have easily had questions that take up more than one row. How would somebody other than you know that? Apparently my question offended you. For that, I apologize. Otto "cy" wrote in message ... you don't know what i mean? let say i have 5q. in a row that is A1(q.1),A2(q.2),A3(q.3),A4(q.4),A5(q.5) how can i insert a column next to each q. so that the seqence now is A1(q.1),A2(blank),A3(q.2)........ but some blank columns have already inserted,so how to write that VBA? "Otto Moehrbach" ??: You must mean rows instead of columns. Yes, VBA can do that, but Excel needs to know how to identify a line as being the beginning of a new question. Pretend that you are explaining how to do this to someone who doesn't know your data. What would you say to him? HTH Otto "cy" wrote in message ... I have 500+ question in a row,how can I insert column in between every questions but there are some questions already inserted with column,is there a method to save time so that each q. will have only 1 blank column in between it? |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to insert column between q.
OK - and thanks for the response. -- cy Wrote: That is awesome,thankyou:D "Bryan Hessey" 來函: try amending to: For iRow = LastRow To FirstRow + 1 Step -1 If .Cells(iRow - 1, 1).Value = "" Then iRow = iRow - 1 Else .Rows(iRow).Resize(1).Insert End If Next iRow -- cy Wrote: oh my..............my blanks is distributed randomly,so is there any method to chg the code to avoid insert blank when matching blanks "Bryan Hessey" ä¾€*Ã¥€¡Â½Ã¯Â¼Å¡ If you have already started to insert blanks, then adjust the number shown as Startrow= from 1 to the first row you need. (in Dave's code) FirstRow = 1 'no headers to FirstRow = 25 'no headers - the number being your first desired row To insert only 1 blank, amend .Rows(iRow).Resize(2).Insert to .Rows(iRow).Resize(1).Insert This should work. --- cy Wrote: You are correct,Bryan i mix up column and row wrongly,and the answer is partly correct only ..It is because like i said before,there are some blank rows already inserted,after running the marco ,there will be 3 blank together,so is there possible to rewrite the macro so that it will not adding a blank row before and after the blank row that already exist? "Bryan Hessey" ä¾â‚¬*倡½ï¼š If you didn't call Row as Column you would be more easily understood For what you require, see Dave Peterson's answer in http://www.excelforum.com/showthread.php?t=537047 -- cy Wrote: you don't know what i mean? let say i have 5q. in a row that is A1(q.1),A2(q.2),A3(q.3),A4(q.4),A5(q.5) how can i insert a column next to each q. so that the seqence now is A1(q.1),A2(blank),A3(q.2)........ but some blank columns have already inserted,so how to write that VBA? "Otto Moehrbach" äÀšÃ‚¾Ã¢â€šÂ¬*倠¡Ã€šÃ‚½ÃƒÂ¯Ã€šÃ‚¼Ã€¦Ã‚¡ You must mean rows instead of columns. Yes, VBA can do that, but Excel needs to know how to identify a line as being the beginning of a new question. Pretend that you are explaining how to do this to someone who doesn't know your data. What would you say to him? HTH Otto "cy" wrote in message ... I have 500+ question in a row,how can I insert column in between every questions but there are some questions already inserted with column,is there a method to save time so that each q. will have only 1 blank column in between it? -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=537608 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=537608 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=537608 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=537608 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert duplicate rows based on numeric value in column | Excel Discussion (Misc queries) | |||
match and count words | Excel Worksheet Functions | |||
Lookup Table Dilemma | Excel Worksheet Functions | |||
Lookup then Match and insert value from next column | Excel Worksheet Functions | |||
How can I sort an entire spreadsheet from a list | Excel Worksheet Functions |