![]() |
Inserting Columns based on missing headers
I have a spreadsheet with columns labeled, for example, X1 X2 X3 X4 X5
X6 X7 X8 X9 X10. I need all 10 columns but where this data pulls from only gives me the columns that have data in the rows below. So if column X2 is blank it does not pull X2 and would give me X1 X3 X4 X5 X6 X7 X8 X9 X10. I found this macro for when there is only 1 column missing: Dim XLCell As Range Set XLCell = Sheets("Test").Range("1:1").Find("X4", , xlValues, xlWhole) If XLCell Is Nothing Then Sheets("Test").Range ("B1").EntireColumn.Insert Sheets("Test").Range("B1").Value = "X4" I can run this 10 times as 10 seperate Subs changing X4 to X1 and then X2 and etc. to make sure I have all of my columns. I suspect there is a better way to do this. Does anyone know a macro that will check row 1 for all of these values and then insert the missing ones? The location that it inserts them in is not important (it could even add them onto the end) because I will be sorting them afterwards. Thank you for the help, Dow. |
Inserting Columns based on missing headers
Hi Dow, In the following example I've set up a For Next loop to work from left to right, checking for the appropriate header of X1,X2...X10. If missing a column is inserted and the header updated as required. Sub Dow() ' We expect 10 column headers X1 to X10. if one is missing we insert a column For i = 1 To 10 If Cells(1, i) < "X" & i Then Cells(1, i).EntireColumn.Insert Cells(1, i) = "X" & i End If Next End Sub Hopefully this works as you require. Anthony |
Inserting Columns based on missing headers
On May 14, 1:13*pm, excel-ant wrote:
Hi Dow, In the following example I've set up a For Next loop to work from left to right, checking for the appropriate header of X1,X2...X10. If missing a column is inserted and the header updated as required. Sub Dow() * * ' We expect 10 column headers X1 to X10. if one is missing we insert a column * * For i = 1 To 10 * * * * If Cells(1, i) < "X" & i Then * * * * * * Cells(1, i).EntireColumn.Insert * * * * * * Cells(1, i) = "X" & i * * * * End If * * Next End Sub Hopefully this works as you require. Anthony That is really cool, Anthony. I can see some places where I will be able to use this concept. I see now that I should not have used somethng as easy as X1, X2 for the example of my column headers. The X1, X2, etc were examples. The actual headings are closer to "advanced", "redo", "undo", "territories", "box 3". Nothing as nice and simple as my example. Do you know of a way to loop through a list of words like this in a macro? Dow. |
Inserting Columns based on missing headers
On May 14, 1:13*pm, excel-ant wrote:
HiDow, In the following example I've set up a For Nextloopto work from left to right, checking for the appropriate header ofX1,X2...X10. If missing a column is inserted and the header updated as required. SubDow() * * ' We expect 10 column headersX1to X10. if one is missing we insert a column * * For i = 1 To 10 * * * * If Cells(1, i) < "X" & i Then * * * * * * Cells(1, i).EntireColumn.Insert * * * * * * Cells(1, i) = "X" & i * * * * End If * * Next End Sub Hopefully this works as you require. Anthony Is there no one else out there willing to take a chance on this one? I realized that I need to do the find with an Array but I still have not had any luck. |
Inserting Columns based on missing headers
see my earlier response to your later question
regards "Dow" wrote in message ... I have a spreadsheet with columns labeled, for example, X1 X2 X3 X4 X5 X6 X7 X8 X9 X10. I need all 10 columns but where this data pulls from only gives me the columns that have data in the rows below. So if column X2 is blank it does not pull X2 and would give me X1 X3 X4 X5 X6 X7 X8 X9 X10. I found this macro for when there is only 1 column missing: Dim XLCell As Range Set XLCell = Sheets("Test").Range("1:1").Find("X4", , xlValues, xlWhole) If XLCell Is Nothing Then Sheets("Test").Range ("B1").EntireColumn.Insert Sheets("Test").Range("B1").Value = "X4" I can run this 10 times as 10 seperate Subs changing X4 to X1 and then X2 and etc. to make sure I have all of my columns. I suspect there is a better way to do this. Does anyone know a macro that will check row 1 for all of these values and then insert the missing ones? The location that it inserts them in is not important (it could even add them onto the end) because I will be sorting them afterwards. Thank you for the help, Dow. |
All times are GMT +1. The time now is 01:23 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com