How find the empty column immediately after the data column???

[sankartalam]

Hi,
I am new to this VBA code...
But have gone through some of the basics...

Presently i am doveloping one application in the Excel...
In this I'll be using the button click and macros (i'll call the macros when the button is clicked)
My Requirement is as follows::
1. I have Tool Bar Button.
2. I have some merged cells in my excel sheet.
I have attached my Excel here...
3. Whenever if i click the button by selecting the merged cells then a new column will have to be added after the last column.
4.It has to find out which range of merged cells were selected before clicking on the button (so that, the new column will be added in that range of rows).


Hope you all got my requirement
Please help me to get started working on this...

Thanks in advance
Sankar.

[Simon Lloyd]

3. Whenever if i click the button by selecting the merged cells then a new column will have to be added after the last column.
4.It has to find out which range of merged cells were selected before clicking on the button (so that, the new column will be added in that range of rows).Sankartalam, in point 3 you want a column added but the columns would naturally be there. In point 4 you state that you want the new "Column" added for a "Range of Rows" you cant add a column in sections its either the entire column or not, when you say add a column do you mean copy the data to next available column?

Can you clarify?

[sankartalam]

Hi Simon Thanks alot for your reply...

in my view point 3&4 adding new column means adding data from the inputBox.
Hope you have my ex1.xls... just open and follow these to get the clarification...
when if i select Mytest and clicking on the button...then it has to check the last data column(column which is having some data) and has to draw the borders for new column(In my Excel, column G1 to G9...) and has to display the inputbox to enter the values into Excel sheet G column from 3 to 9 rows.


I am attaching final output Excel sheet here...


I have some other issues on the same..ill take your help after got the solution for this...



Thanks
Sankar

[Simon Lloyd]

I see no button on your worksheet!
1. Each block at the end of the rows has a set of merged cells, it's not just a case of re-drawing borders.

2. You want an input box to pop up 6 times to fill data in G3 - G9 (G6 is left blank) what about the other data in the block H7 - H9?

3. Does the block have to have merged cells?

4. Does the block need titles?

5. Will it always be for rows 1 - 9?

6. Do the blocks need numbering?

7. Have you tried recording some of the actions to get you started on your way to having something useable and then ask for help making it work for you rather than asking someone to do all the work without so much as a clear explanation from you?

[sankartalam]

Hi Simon,

I have done recording and some coding also...

The Excel which i sent you as an attachment is the output which i want after my all work done with the button click and macro calling in that button click...

Now I am sending the basic excel ,to which we will have to attach toolbar button and macro to perform the operations to get the final output which i sent you previously...


I am not expecting whole work done by you...

Help me in the following issues for that i'll be thankfull to you...

1. How to check last data column???
(In my Ex1.xls Parameter Name (i.e., F) column is the final Data Column)
if you send me any sample code that would be great.

2. If i selected one cell in column "B",
then how to check the previos coulmn of the same row cell is merged or not? (I can check the selected cell is merged or not by Selection.MergeCells = True).

3. how to use do while functionality???
could you please send me a sample code?

[Simon Lloyd]

You have not answered any of my questions, you have not supplied any code you are working with and just continue blatantly!

You don't deserve this but out of the goodness of my heart!

Sub Find_Column_Copy_To_Next()
Dim LCol As Integer
If WorksheetFunction.CountA(Cells) > 0 Then
LCol = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
End If
Columns(LCol).Copy Columns(LCol + 1)
Columns(LCol + 1).ClearContents
End Sub

Sub merged_cell_check()
If ActiveCell.Column = 1 Then Exit Sub
If ActiveCell.Offset(0, -1).MergeCells <> True Then
MsgBox "Not Merged"
Else
MsgBox "Merged"
End If
End Sub

[sankartalam]

Hi Simon,

I apologige for my Mistake...

I thought if I write answers for you then you may get confusion again because of that reason i didnt answer them



Now I am answering for your questions

I have already attached a toolbar button as an Add_In in my Macro Enabled Excel

1.
Each block at the end of the rows has a set of merged cells, it's not just a case of re-drawing borders.


We have to draw borders depending on the merged cell selection.
suppose if we select MyTest then border drawing is for G column from G1 to G9 as showing in the Output Excel. and if the selection is MyTest1 then the drawing is from G10 to G17.

2.

You want an input box to pop up 6 times to fill data in G3 - G9 (G6 is left blank) what about the other data in the block H7 - H9?

The Input box is popup 6 times when selecting the merged cell MyTest and clicking on the button.
But For cells H7-H9 the input box shoud popup 3 times only when selecting the testcomponent2 and clicking on the button.

3.

Does the block have to have merged cells?


We may require in the future..But at present we dnt need.

4.

Does the block need titles?


Yes we need. They are like Iterations1,Iterations2, etc.

5.

Will it always be for rows 1 - 9?


No its not always 1-9... when selecting Mytest its 1-9..
when selecting Mytest1 its 10-17.

6.

Do the blocks need numbering?

yes needs..they are like iterations1, iterations2...

7.

Have you tried recording some of the actions to get you started on your way to having something useable and then ask for help making it work for you rather than asking someone to do all the work without so much as a clear explanation from you?


I have recorded and tried to change the code as per my requirement. code is as follows


Sub Iterate()
' Iterate Macro
'Dim i As Range
If ActiveCell.Offset(0, -1).MergeCells = True Then
Range("G1").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
ActiveCell.FormulaR1C1 = "Iteration1"
Range("G1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("G2").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("G9").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("G2:G9").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("G5").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("G6").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
For i = 3 To 9
If i = 6 Then
i = i + 1
End If
Range("G" & i).Select
ActiveCell.FormulaR1C1 = InputBox("Enter Value")
Next I
Range("H1").Select
' End If
Else: Selection.MergeCells = False
MsgBox "Selected Item Does Not Have Run Time Iterations"
End If
End Sub



:help