?
Solved

Delete MSHFlexgrid1 column if

Posted on 2011-05-11
25
Medium Priority
?
591 Views
Last Modified: 2012-06-22
Hello all

I would like to delete column in my MSHFlexgrid1 if the column name = "Cust Name" or "Week #" or "Effective Time" or "Effective Time"

How can i do that?

Is it possible?

Thanks for your help

Really appreciate.
0
Comment
Question by:Wilder1626
  • 10
  • 8
  • 6
  • +1
25 Comments
 
LVL 11

Author Comment

by:Wilder1626
ID: 35747212
I searching on this but we talk a lot about deleting rows but not column.

I guess it is not possible.

I'm i right?

Thanks again
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35747519
set the column width = 0
It would be faster than deleting the column or rebuilding the grid.
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 35747561
Hello aikimark.

The problem with this option is that in it will create another issue on my next code. That is why i wanted to delete the column.

I could deleted the column in the excel sheet to prevent that issue when i import in the grid but i was just trying to make it easier for me since i do this step many time per day and that excel sheet that i upload comes from many users.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 46

Expert Comment

by:aikimark
ID: 35747731
please post a comment with the context of the question.  If the experts don't know the context, you won't get the optimal solution.
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 35747775
Ok, i will do this.

Be back soon.

Thanks again
0
 
LVL 14

Expert Comment

by:VBClassicGuy
ID: 35750358
Too bad the column to be deleted is not always the last column. If it was, you could use:

MSFlexGrid1.Cols = MSFlexGrid1.Cols - 1
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 35750989
hello VBClassicGuy.

Oh, your right.

But no. sorry. they are all in the middle.

0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 35753369
not sure how efficient it is but it works for me. probably only for small data sets or the string gets long.
Private Sub Command1_Click()
     'load test data
      For i = 0 To 4
   
        For j = 0 To 4
   
          MSFlexGrid1.TextMatrix(i, j) = i + j
   
        Next
   
      Next
End Sub

Private Sub Command2_Click()
   
      Dim sDump As String
      
      sDump = ""
      skipcols = 0
    
      'process data rows
      For i = 0 To MSFlexGrid1.Rows - 1
   
        For j = 0 To MSFlexGrid1.Cols - 1
          Select Case MSFlexGrid1.TextMatrix(0, j)
            'the header cell of column
            Case "Cust Name", "Week #", "Effective Time", "Effective Time", "3"
                'skip it
                If i = 0 Then
                    'count the headers deleted
                    skipcols = skipcols + 1
                End If
            Case Else
                sDump = sDump & MSFlexGrid1.TextMatrix(i, j) & vbTab
                
          End Select
          
        Next j
       
        sDump = Left$(sDump, Len(sDump) - 1) & vbCr 'terminate the row by replacing the tab
   
      Next i
    
      'reduce the column count
      MSFlexGrid1.Cols = MSFlexGrid1.Cols - skipcols
      
      With MSFlexGrid1
        .Clear
        .Row = 0    '("select all")
        .Col = 0
  
        .RowSel = MSFlexGrid1.Rows - 1
        .ColSel = MSFlexGrid1.Cols - 1
  
        .Clip = sDump
  
        .RowSel = 0  'reset selection
        .ColSel = 0
  
      End With
End Sub

Open in new window

0
 
LVL 46

Expert Comment

by:aikimark
ID: 35753425
@robberbaron

For efficiency, place the column-selection loop outside of the row copying loop.  Basically, you are going to iterate all the columns and, conditionally, the target column.  If the source and target column numbers are the same, you don't need to copy the rows, saving you lots of (wasted) data movement prior to the first to-be-deleted column.
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 35753916
Hello all

Last night, i was starting to think that it would be easier to just remove the columns in the excel sheet.

I'm happy that there is a way.

Let me try this.

Thanks again for your help.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35753945
If you do delete Excel columns (see hint below), iterate them in last-to-first order.
range("M1").EntireColumn.Delete

Open in new window

0
 
LVL 11

Author Comment

by:Wilder1626
ID: 35754065
Hello aikimark

Not sure to understand that part here:       
range("M1").EntireColumn.Delete

Open in new window


Sorry

Where would i put that code?
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35754082
After you transfer the data to Excel (I assume via Excel automation), you would then iterate the target worksheet to delete the columns.
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 35754118
Oh! actually, i don't transfer the data back to excel at that stage.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35754154
Whenever you DO transfer the data.  The Excel column deletion would happen then.
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 35754745
ok,

I will take a look at it later. But thanks for the idea. I think it will be useful.
0
 
LVL 14

Expert Comment

by:VBClassicGuy
ID: 35756276
Found an easy way to delete any column. This example uses a command button to trigger the delete, but you can call the RemoveColumn routine from anywhere:

Private Sub Command1_Click()
   Dim ColNum As Long
   ColNum = 1   'set the column you want deleted here
   Call RemoveColumn(ColNum)
End Sub

Private Sub RemoveColumn(ColNum)

   With MSFlexGrid1
   'set column to delete
   .Col = ColNum
   'moving the selected column to be the last column of the grid
   .ColPosition(.Col) = .Cols - 1
   End With
   
   'reducing number of columns by one.. thus removing the last one
   MSFlexGrid1.Cols = MSFlexGrid1.Cols - 1

End Sub
0
 
LVL 14

Accepted Solution

by:
VBClassicGuy earned 1200 total points
ID: 35756343
For your exact situation, you would use:

Private Sub Command1_Click()
   Dim ColNum As Long, i As Long
   ColNum = -1
   For i = 0 To MSFlexGrid1.Cols - 1
      Select Case MSFlexGrid1.TextMatrix(0, i)
         Case "Cust Name", "Week #", "Effective Time"
            ColNum = i   'set the column you want deleted here
            Exit For
      End Select
   Next i
   If ColNum >= 0 Then
      Call RemoveColumn(ColNum)
   End If
End Sub

Private Sub RemoveColumn(ColNum)

   With MSFlexGrid1
   'set column to delete
   .Col = ColNum
   'moving the selected column to be the last column of the grid
   .ColPosition(.Col) = .Cols - 1
   End With
   
   'reducing number of columns by one.. thus removing the last one
   MSFlexGrid1.Cols = MSFlexGrid1.Cols - 1

End Sub
0
 
LVL 46

Assisted Solution

by:aikimark
aikimark earned 800 total points
ID: 35756422
@VBClassicGuy

That's very elegant and most excellent.

If you iterate from back-to-front (right-to-left), you should be able to delete all of them in one pass.

Private Sub cmdDeleteColumns_Click()
   Dim ColNum As Long, ColCount As Long
   ColCount = MSFlexGrid1.Cols
   For ColNum = (ColCount - 1) To 0 Step -1
      Select Case MSFlexGrid1.TextMatrix(0, ColNum)
         Case "Cust Name", "Week #", "Effective Time"
            Call RemoveColumn(ColNum)
      End Select
   Next ColNum
End Sub

Open in new window

0
 
LVL 14

Expert Comment

by:VBClassicGuy
ID: 35756453
One last finishing touch. If you wish to shore up the FlexGrid and remove the "dead space" left by the deleted column, use this routine instead:

Private Sub RemoveColumn(ColNum)

   Dim ColumnWidth As Long
   ColumnWidth = MSFlexGrid1.ColWidth(ColNum)
   
   With MSFlexGrid1
   'set column to delete
   .Col = ColNum
   'moving the selected column to be the last column of the grid
   .ColPosition(.Col) = .Cols - 1
   End With
   
   'reducing number of columns by one.. thus removing the last one
   MSFlexGrid1.Cols = MSFlexGrid1.Cols - 1

   'resize FlexGrid
   MSFlexGrid.Width = MSFlexGrid.Width - ColumnWidth

End Sub
0
 
LVL 14

Expert Comment

by:VBClassicGuy
ID: 35756463
@aikimark

Thank you. And good code for doing it in one pass too!
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 35756732
wow, let me take a look at all this .

But again wow!!!!!!!!!

I will let you know the result.

Thanks again for your help.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35757278
@VBClassicGuy

I was simply building on your genius idea -- move it to the end and truncate...brilliant!!
0
 
LVL 14

Expert Comment

by:VBClassicGuy
ID: 35757357
Gee thanks, I'm blushing. But I'm not the real Genius here, your track record and rank speaks for itself.
0
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 35764370
Thanks you so much, this is working great.
Thanks again for your help.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month16 days, 10 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question