Delete MSHFlexgrid1 column if

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

set the column width = 0
It would be faster than deleting the column or rebuilding the grid.

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.

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.

Ok, i will do this.

Be back soon.

Thanks again

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

MSFlexGrid1.Cols = MSFlexGrid1.Cols - 1

hello VBClassicGuy.

Oh, your right.

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

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

Select allOpen in new window

@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.

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.

If you do delete Excel columns (see hint below), iterate them in last-to-first order.

range("M1").EntireColumn.Delete

Select allOpen in new window

Hello aikimark

Not sure to understand that part here:       

range("M1").EntireColumn.Delete

Select allOpen in new window

Sorry

Where would i put that code?

After you transfer the data to Excel (I assume via Excel automation), you would then iterate the target worksheet to delete the columns.

Oh! actually, i don't transfer the data back to excel at that stage.

Whenever you DO transfer the data.  The Excel column deletion would happen then.

ok,

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

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

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

@aikimark

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

wow, let me take a look at all this .

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

I will let you know the result.

Thanks again for your help.

@VBClassicGuy

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

Gee thanks, I'm blushing. But I'm not the real Genius here, your track record and rank speaks for itself.

Thanks you so much, this is working great.
Thanks again for your help.