Linux HowTo: How to pivot (transpose) data sets of differing length

Original Source Link

I have 5000+ records in Column A. Each record contains a number of rows of data. The number of rows containing data varies from record to record. How do I transpose the data in these records so that each record occupies only one row? See included image. (using Excel included in Microsoft 365)

enter image description here

Combination of IFERROR & INDEX command, and few helper value solves the issue.

::Caveat::

  • Shown method works with any version of Excel.
  • It’s not an smart method but solves the issue.
  • To improvise this exercise, readers may suggest
    an efficient Formula/Macro.

enter image description here

How it works:

1. Insert series of numbers (I93:M93) based on maximum elements per record set, here maximum is 5.

2. Enter another series of numbers (H94:H96) based on record set, here are 3.

3. Formula in cell I94:

 =IF($H94=1,IFERROR(INDEX($I$78:$I$82,I$93,1),""),IF($H94=2,IFERROR(INDEX($I$84:$I$86,I$93,1),""),IF($H94=3,IFERROR(INDEX($I$88:$I$92,I$93,1),""),"")))

4, Fill it across.

Adjust cell references in the formula as needed.


::Edited::

Since OP is handling huge data set (around 5000 records) therefore VBA Macro can a smart way to handle the issue.

Sub TransposeWithBlanks()
 Dim Data_Array
 Dim OutPut_Array()
 Dim LR As Long, Counter As Long, LR2 As Long
 Dim i As Long

 Application.ScreenUpdating = False

  With Sheets("Sheet1")
     LR = .Cells(Rows.Count, "A").End(xlUp).Row + 1
     Data_Array = .Range("A1:A" & LR).Value2
  End With


    On Error Resume Next
    For i = LBound(Data_Array, 1) To UBound(Data_Array, 1)

       If Trim(Data_Array(i, 1)) <> vbNullString Then
            Counter = Counter + 1

            ReDim Preserve OutPut_Array(1 To 1, 1 To Counter)

            OutPut_Array(1, Counter) = Data_Array(i, 1)

        Else

            With Sheets("Sheet1")
                LR2 = .Cells(Rows.Count, "C").End(xlUp).Row
                .Range("C" & LR2 + 1).Resize(1, Counter).Value2 = OutPut_Array
            End With

            Counter = 0
        End If
    Next i

End Sub

enter image description here

N.B.

  • Save the workbook as Macro Enabled.
  • In Above code Sheet name and data range are editable.
  • If you want to Paste the Transposed data in another Sheet, then you need to edit this portion of the code.

    With Sheets("Sheet1")
                LR2 = .Cells(Rows.Count, "C").End(xlUp).Row
                .Range("C" & LR2 + 1).Resize(1, Counter).Value2 = OutPut_Array
            End With
    

that is very kind of you to look at this challenge. While your formula works, it expects to be told the cell range for each individual record ($I$84:$I$86 for Record 2…then $I$88:$I$92 for Record 3… and so on. With 5000 records, one would need to input the ranges of each of the 5000 individual records – which is not really practical.

I am not an excel guru, so please excuse my following ‘layman’s’ thoughts.

The formula (or more likely macro?) that I think would work is as follows (using your table above):
1) Instruct the macro to search downwards in column I for the word (string?) “Record”.
2) Each time the macro finds the word “Record” instruct it to copy/paste the data in the subsequent cells immediately below, horizontally into the adjacent cells (to its right) – and stop the copy/paste when it reaches the next cell down with another word “record”.

As I said, I don’t know how to ‘program’ formulas or macros in excel – so my suggestion may be totally not possible.

Anyhow, regardless, you have been very kind to look at this. I hope someone has come across this problem before, and has a workable answer.

Yours kindly, Sean

Tagged : / /

Making Game: How to pivot (transpose) data sets of differing length

Original Source Link

I have 5000+ records in Column A. Each record contains a number of rows of data. The number of rows containing data varies from record to record. How do I transpose the data in these records so that each record occupies only one row? See included image. (using Excel included in Microsoft 365)

enter image description here

Combination of IFERROR & INDEX command, and few helper value solves the issue.

::Caveat::

  • Shown method works with any version of Excel.
  • It’s not an smart method but solves the issue.
  • To improvise this exercise, readers may suggest
    an efficient Formula/Macro.

enter image description here

How it works:

1. Insert series of numbers (I93:M93) based on maximum elements per record set, here maximum is 5.

2. Enter another series of numbers (H94:H96) based on record set, here are 3.

3. Formula in cell I94:

 =IF($H94=1,IFERROR(INDEX($I$78:$I$82,I$93,1),""),IF($H94=2,IFERROR(INDEX($I$84:$I$86,I$93,1),""),IF($H94=3,IFERROR(INDEX($I$88:$I$92,I$93,1),""),"")))

4, Fill it across.

Adjust cell references in the formula as needed.


::Edited::

Since OP is handling huge data set (around 5000 records) therefore VBA Macro can a smart way to handle the issue.

Sub TransposeWithBlanks()
 Dim Data_Array
 Dim OutPut_Array()
 Dim LR As Long, Counter As Long, LR2 As Long
 Dim i As Long

 Application.ScreenUpdating = False

  With Sheets("Sheet1")
     LR = .Cells(Rows.Count, "A").End(xlUp).Row + 1
     Data_Array = .Range("A1:A" & LR).Value2
  End With


    On Error Resume Next
    For i = LBound(Data_Array, 1) To UBound(Data_Array, 1)

       If Trim(Data_Array(i, 1)) <> vbNullString Then
            Counter = Counter + 1

            ReDim Preserve OutPut_Array(1 To 1, 1 To Counter)

            OutPut_Array(1, Counter) = Data_Array(i, 1)

        Else

            With Sheets("Sheet1")
                LR2 = .Cells(Rows.Count, "C").End(xlUp).Row
                .Range("C" & LR2 + 1).Resize(1, Counter).Value2 = OutPut_Array
            End With

            Counter = 0
        End If
    Next i

End Sub

enter image description here

N.B.

  • Save the workbook as Macro Enabled.
  • In Above code Sheet name and data range are editable.
  • If you want to Paste the Transposed data in another Sheet, then you need to edit this portion of the code.

    With Sheets("Sheet1")
                LR2 = .Cells(Rows.Count, "C").End(xlUp).Row
                .Range("C" & LR2 + 1).Resize(1, Counter).Value2 = OutPut_Array
            End With
    

that is very kind of you to look at this challenge. While your formula works, it expects to be told the cell range for each individual record ($I$84:$I$86 for Record 2…then $I$88:$I$92 for Record 3… and so on. With 5000 records, one would need to input the ranges of each of the 5000 individual records – which is not really practical.

I am not an excel guru, so please excuse my following ‘layman’s’ thoughts.

The formula (or more likely macro?) that I think would work is as follows (using your table above):
1) Instruct the macro to search downwards in column I for the word (string?) “Record”.
2) Each time the macro finds the word “Record” instruct it to copy/paste the data in the subsequent cells immediately below, horizontally into the adjacent cells (to its right) – and stop the copy/paste when it reaches the next cell down with another word “record”.

As I said, I don’t know how to ‘program’ formulas or macros in excel – so my suggestion may be totally not possible.

Anyhow, regardless, you have been very kind to look at this. I hope someone has come across this problem before, and has a workable answer.

Yours kindly, Sean

Tagged : / /

Code Bug Fix: Removing Strikethrough text and copying to a new column is missing the first dot

Original Source Link

Hi I am using this code to remove any text in a cell that is striked through and copying it to a new column cell.. Its removing the strike through text but when copying the text to a new cell it misses the first dot after the 1. i.e

1.0900 - 1800     its copying as   10900 - 1800
2.0830 - 1700                      2.0830 - 1700 

For Each cel In rng
For i = 1 To Len(cel.Value)
If cel.Characters(i, 1).Font.Strikethrough = False Then
cel.Offset(0, -1).Value = cel.Offset(0, -1).Value & cel.Characters(i, 1).Text

End If
Next

Any idea why its missing the dot? It only does it on the first line on each cell.

The issue is that you write one character after another and if you write 1. into a cell it gets reduced to 1 automatically.

For Each cel In rng
    Dim OutputText As String
    OutputText = vbNullString 'initialize

    For i = 1 To Len(cel.Value)
        If cel.Characters(i, 1).Font.Strikethrough = False Then
            OutputText = OutputText & cel.Characters(i, 1).Text
        End If
    Next i

    cel.Offset(0, -1).Value = OutputText
Next cel

So put all the characters together in a variable and write all at once in the end is even much faster.

Tagged : / /

Code Bug Fix: VBA- how to duplicate values

Original Source Link

I’m having a bit of a problem with my VBA project. I have a list of unique values in column A (300 values, let’s say) and in column B I need to triple each value (so I would get 900 values, each x3) and in column C I need to assign them: A, B, C, A, B, C, A, B, C, etc. so it would look like:

col A: val1 val2 val3 …

col B: val1 val1 val1 val2 val2 val2 …

col C: A B C A B C …

I’m stuck on this, so I would appreciate some tips as I couldn’t find anything even similar on stack, usually it’s just removing duplicates

Dim rowCount As Integer
Dim lrow As Long
Dim rowNum As Variant, element As Variant

lrow = ThisWorkbook.Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row
rowCount = 1
assignArrayValue = Array("A", "B", "C")
For rowNum = 1 To lrow
    For Each element In assignArrayValue
        ThisWorkbook.Worksheets(1).Cells(rowCount, "B").Value = ThisWorkbook.Worksheets(1).Cells(rowNum, "A").Value
        ThisWorkbook.Worksheets(1).Cells(rowCount, "C").Value = element
        rowCount = rowCount + 1
    Next
Next

I placed the values that you want repeated in the column A of the worksheet one below another. I have taken some static values such as the columns, worksheets and array as well, check and see if the above code helps in any way. Goodluck!

Tagged : / /

Code Bug Fix: Calculate combinations of inputs/outputs

Original Source Link

There are 3 worksheets (‘directions’, ‘calculation’, ‘combinations’) in a single Excel file, each looks like the following:

‘directions’ sheet:
'directions' sheet
this is the only sheet the user should have interaction with; the user basically copies information about sheet ‘calculation’

‘calculation’ sheet:
'calculation' sheet
this sheet contains list of inputs (simple integers) and outputs (I’ve added column G which explain which formulas are used to calculate outputs from column F)

The aim is to create a VBA script that inserts values from ‘directions’ column C into ‘calculations’ column D, then extracts resulting outputs from column F of ‘calculations’ and saves everything in a single table in sheet ‘combinations’. The resulting table should basically have all combinations of all inputs and respective outputs, looking like:
this

The problem is this solution is not scalable, meaning that now it works only with exactly 3 inputs and 4 outputs. With a different number of inputs/outputs, the script would require manual adjustment to work properly (adding new variables, adding new layers to FOR loop, etc.). Is there a way to make this script self-adjustable for any number of inputs and outputs?

The script I’ve used here:

Sub AllCombinations()

Application.ScreenUpdating = False

Dim thisWB As Workbook
Dim sheetDirections As Worksheet, sheetCalc As Worksheet, sheetComb As Worksheet
Dim input1 As Integer, input2 As Integer, input3 As Integer
Dim output1 As Integer, output2 As Integer, output3 As Integer, output4 As Integer
Dim inputValues1() As String, inputValues2() As String, inputValues3() As String
Dim inputCoords1 As String, intputCoords2 As String, inputCoords3 As String
Dim outputCoords1 As String, outputCoords2 As String, outputCoords3 As String
Dim i1 As Integer, i2 As Integer, i3 As Integer
Dim rowNr As Integer

Set thisWB = ThisWorkbook
Set sheetDirections = thisWB.Worksheets("directions")
Set sheetCalc = thisWB.Worksheets("calculation")
Set sheetComb = thisWB.Worksheets("combinations")

' save coordinates of input/output
inputCoords1 = sheetDirections.Cells(2, 2).Value
inputCoords2 = sheetDirections.Cells(3, 2).Value
inputCoords3 = sheetDirections.Cells(4, 2).Value
outputCoords1 = sheetDirections.Cells(2, 6).Value
outputCoords2 = sheetDirections.Cells(3, 6).Value
outputCoords3 = sheetDirections.Cells(4, 6).Value
outputCoords4 = sheetDirections.Cells(5, 6).Value

' clear whole sheet before use
sheetComb.Cells.Clear

' print names in first row
sheetComb.Cells(1, 1).Value = sheetDirections.Cells(2, 1).Value
sheetComb.Cells(1, 2).Value = sheetDirections.Cells(3, 1).Value
sheetComb.Cells(1, 3).Value = sheetDirections.Cells(4, 1).Value
sheetComb.Cells(1, 4).Value = sheetDirections.Cells(2, 5).Value
sheetComb.Cells(1, 5).Value = sheetDirections.Cells(3, 5).Value
sheetComb.Cells(1, 6).Value = sheetDirections.Cells(4, 5).Value
sheetComb.Cells(1, 7).Value = sheetDirections.Cells(5, 5).Value

' split input variables, separator is ';'
inputValues1 = Split(sheetDirections.Range("C2").Value, ";")
inputValues2 = Split(sheetDirections.Range("C3").Value, ";")
inputValues3 = Split(sheetDirections.Range("C4").Value, ";")

' input/output calculation and printing
rowNr = 2
For i1 = 0 To UBound(inputValues1)
    For i2 = 0 To UBound(inputValues2)
        For i3 = 0 To UBound(inputValues3)
            ' inputs - print
            sheetComb.Cells(rowNr, 1).Value = inputValues1(i1)
            sheetComb.Cells(rowNr, 2).Value = inputValues2(i2)
            sheetComb.Cells(rowNr, 3).Value = inputValues3(i3)
            ' outputs - insert
            sheetCalc.Range(inputCoords1).Value = inputValues1(i1)
            sheetCalc.Range(inputCoords2).Value = inputValues2(i2)
            sheetCalc.Range(inputCoords3).Value = inputValues2(i3)
            ' outputs - print
            sheetComb.Cells(rowNr, 4).Value = sheetCalc.Range(outputCoords1).Value
            sheetComb.Cells(rowNr, 5).Value = sheetCalc.Range(outputCoords2).Value
            sheetComb.Cells(rowNr, 6).Value = sheetCalc.Range(outputCoords3).Value
            sheetComb.Cells(rowNr, 7).Value = sheetCalc.Range(outputCoords4).Value

            rowNr = rowNr + 1
        Next i3
    Next i2
Next i1

Application.ScreenUpdating = True

End Sub

Tagged : /

Code Bug Fix: Outlook crashes intermittently when using VBA code to copy table from excel and populate an email with the table

Original Source Link

Completely perplexed by an intermittent error that occurs with a piece of code I’ve written/repurposed to copy a table from excel, upload it to a database, then copy that same table into an email.

One member of my team says it crashes his Outlook 50% of the time, kills it off completely no error message or hanging.

Wondering if its maybe running too fast and I need to put a wait command in?

The part of the code used to copy to the email is below:

    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object

    Set rng = Nothing
    On Error Resume Next
    'Only the visible cells in the selection
    'Set rng = Selection.SpecialCells(xlCellTypeVisible)
    'You can also use a fixed range if you want
    Set rng = Sheets("Input Sheet").Range("A1:B23").SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If rng Is Nothing Then
        MsgBox "The selection is not a range or the sheet is protected" & _
               vbNewLine & "please correct and try again.", vbOKOnly
        Exit Sub
    End If

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next

            With OutMail
            .To = "[email protected]"
            .CC = ""
            .BCC = ""
            .Subject = "Product Submission: " & Sheets("Input Sheet").Range("B3") & " - " & Sheets("Input Sheet").Range("B4")
            .HTMLBody = RangetoHTML(rng)
            '.Body = Selection.Paste
            .Display   'or use .Send
    End With
    On Error GoTo 0

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing

and calls the following function to covert table to HTML

Function RangetoHTML(rng As Range)

    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook

    TempFile = Environ$("temp") & "" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

    'Copy the range and create a new workbook to past the data in
    rng.Copy
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial xlPasteValues, , False, False
        .Cells(1).PasteSpecial xlPasteFormats, , False, False
        .Cells(1).Select
        Application.CutCopyMode = False
        On Error Resume Next
        .DrawingObjects.Visible = True
        .DrawingObjects.Delete
        On Error GoTo 0
    End With

    'Publish the sheet to a html file
    With TempWB.PublishObjects.Add( _
         SourceType:=xlSourceRange, _
         Filename:=TempFile, _
         Sheet:=TempWB.Sheets(1).Name, _
         Source:=TempWB.Sheets(1).UsedRange.Address, _
         HtmlType:=xlHtmlStatic)
        .Publish (True)
    End With

    'Read all data from the htm file into RangetoHTML
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.readall
    ts.Close
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                          "align=left x:publishsource=")

    'Close TempWB
    TempWB.Close savechanges:=False

    'Delete the htm file we used in this function
    Kill TempFile

    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
End Function

Thanks in advance for any help/observations

G

Tagged : / / /

Code Bug Fix: Check if the file exists using VBA

Original Source Link

Sub test()

thesentence = InputBox("Type the filename with full extension", "Raw Data File")

Range("A1").Value = thesentence

If Dir("thesentence") <> "" Then
    MsgBox "File exists."
Else
    MsgBox "File doesn't exist."
End If

End Sub

In this when i pickup the text value from the input box, it doesn’t work. If however, if remove "the sentence" from If Dir() and replace it with an actual name in the code, it works. Can somebody help?

Note your code contains Dir("thesentence") which should be Dir(thesentence).

Change your code to this

Sub test()

thesentence = InputBox("Type the filename with full extension", "Raw Data File")

Range("A1").Value = thesentence

If Dir(thesentence) <> "" Then
    MsgBox "File exists."
Else
    MsgBox "File doesn't exist."
End If

End Sub

Use the Office FileDialog object to have the user pick a file from the filesystem. Add a reference in your VB project or in the VBA editor to Microsoft Office Library and look in the help. This is much better than having people enter full paths.

Here is an example using msoFileDialogFilePicker to allow the user to choose multiple files. You could also use msoFileDialogOpen.

'Note: this is Excel VBA code
Public Sub LogReader()
    Dim Pos As Long
    Dim Dialog As Office.FileDialog
    Set Dialog = Application.FileDialog(msoFileDialogFilePicker)

    With Dialog
        .AllowMultiSelect = True
        .ButtonName = "C&onvert"
        .Filters.Clear
        .Filters.Add "Log Files", "*.log", 1
        .Title = "Convert Logs to Excel Files"
        .InitialFileName = "C:InitialPath"
        .InitialView = msoFileDialogViewList

        If .Show Then
            For Pos = 1 To .SelectedItems.Count
                LogRead .SelectedItems.Item(Pos) ' process each file
            Next
        End If
    End With
End Sub

There are lots of options, so you’ll need to see the full help files to understand all that is possible. You could start with Office 2007 FileDialog object (of course, you’ll need to find the correct help for the version you’re using).

Correction to fileExists from @UberNubIsTrue :

Function fileExists(s_directory As String, s_fileName As String) As Boolean

  Dim obj_fso As Object, obj_dir As Object, obj_file As Object
  Dim ret As Boolean
   Set obj_fso = CreateObject("Scripting.FileSystemObject")
   Set obj_dir = obj_fso.GetFolder(s_directory)
   ret = False
   For Each obj_file In obj_dir.Files
     If obj_fso.fileExists(s_directory & "" & s_fileName) = True Then
        ret = True
        Exit For
      End If
   Next

   Set obj_fso = Nothing
   Set obj_dir = Nothing
   fileExists = ret

 End Function

EDIT: shortened version

' Check if a file exists
Function fileExists(s_directory As String, s_fileName As String) As Boolean

    Dim obj_fso As Object

    Set obj_fso = CreateObject("Scripting.FileSystemObject")
    fileExists = obj_fso.fileExists(s_directory & "" & s_fileName)

End Function

just get rid of those speech marks

Sub test()

Dim thesentence As String

thesentence = InputBox("Type the filename with full extension", "Raw Data File")

Range("A1").Value = thesentence

If Dir(thesentence) <> "" Then
    MsgBox "File exists."
Else
    MsgBox "File doesn't exist."
End If

End Sub

This is the one I like:

Option Explicit

Enum IsFileOpenStatus
    ExistsAndClosedOrReadOnly = 0
    ExistsAndOpenSoBlocked = 1
    NotExists = 2
End Enum


Function IsFileReadOnlyOpen(FileName As String) As IsFileOpenStatus

With New FileSystemObject
    If Not .FileExists(FileName) Then
        IsFileReadOnlyOpen = 2  '  NotExists = 2
        Exit Function 'Or not - I don't know if you want to create the file or exit in that case.
    End If
End With

Dim iFilenum As Long
Dim iErr As Long
On Error Resume Next
    iFilenum = FreeFile()
    Open FileName For Input Lock Read As #iFilenum
    Close iFilenum
    iErr = Err
On Error GoTo 0

Select Case iErr
    Case 0: IsFileReadOnlyOpen = 0 'ExistsAndClosedOrReadOnly = 0
    Case 70: IsFileReadOnlyOpen = 1 'ExistsAndOpenSoBlocked = 1
    Case Else: IsFileReadOnlyOpen = 1 'Error iErr
End Select

End Function    'IsFileReadOnlyOpen

Function FileExists(fullFileName As String) As Boolean
    FileExists = VBA.Len(VBA.Dir(fullFileName)) > 0
End Function

Works very well, almost, at my site. If I call it with “” the empty string, Dir returns “connection.odc“!! Would be great if you guys could share your result.

Anyway, I do like this:

Function FileExists(fullFileName As String) As Boolean
  If fullFileName = "" Then
    FileExists = False
  Else
    FileExists = VBA.Len(VBA.Dir(fullFileName)) > 0
  End If
End Function

I’m not certain what’s wrong with your code specifically, but I use this function I found online (URL in the comments) for checking if a file exists:

Private Function File_Exists(ByVal sPathName As String, Optional Directory As Boolean) As Boolean
    'Code from internet: http://vbadud.blogspot.com/2007/04/vba-function-to-check-file-existence.html
    'Returns True if the passed sPathName exist
    'Otherwise returns False
    On Error Resume Next
    If sPathName <> "" Then

        If IsMissing(Directory) Or Directory = False Then

            File_Exists = (Dir$(sPathName) <> "")
        Else

            File_Exists = (Dir$(sPathName, vbDirectory) <> "")
        End If

    End If
End Function

Function FileExists(fullFileName As String) As Boolean
    FileExists = VBA.Len(VBA.Dir(fullFileName)) > 0
End Function

Very old post, but since it helped me after I made some modifications, I thought I’d share. If you’re checking to see if a directory exists, you’ll want to add the vbDirectory argument to the Dir function, otherwise you’ll return 0 each time. (Edit: this was in response to Roy’s answer, but I accidentally made it a regular answer.)

Private Function FileExists(fullFileName As String) As Boolean
    FileExists = Len(Dir(fullFileName, vbDirectory)) > 0
End Function

based on other answers here I’d like to share my one-liners that should work for dirs and files:

  • Len(Dir(path)) > 0 or Or Len(Dir(path, vbDirectory)) > 0  'version 1 - ... <> "" should be more inefficient generally
    
    • (just Len(Dir(path)) did not work for directories (Excel 2010 / Win7))
  • CreateObject("Scripting.FileSystemObject").FileExists(path)  'version 2 - could be faster sometimes, but only works for files (tested on Excel 2010/Win7)
    

as PathExists(path) function:

Public Function PathExists(path As String) As Boolean
    PathExists = Len(Dir(path)) > 0 Or Len(Dir(path, vbDirectory)) > 0
End Function

Tagged : /

Linux HowTo: Turning off “Drag Fill Series” in Excel

Original Source Link

I am ideally looking to only turn off the drag fill series functionality.

I only want the user of my sheet to be able to drag COPY down.

I turned the feature off in settings, which disable ALL drag downs. I would be happy with that, but of course this only works in my own Excel settings, other users of the sheet will still be able too in the context of their settings.

I am wondering if there is a way in VBA to turnoff the fill series features or disable the drag feature entirely?

The biggest challenge you will face is Macro Security on the individual machines. If they are not set to run Macros this will keep this from running.

You can use the following in VBA to disable Cell Drag and Drop when the workbook opens.

Private Sub Workbook_Open()
Application.CellDragAndDrop = False
End Sub

In order to avoid making people angry, you should turn it back on when the workbook closes.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CellDragAndDrop = True
End Sub

You can use the IF statement to check if the feature is turned on off/on to ensure you return it to the original state. The biggest challenge you will face is Macro Security on the individual machines. If they are not set to run Macros this will keep this from running.

Tagged : /

Making Game: Turning off “Drag Fill Series” in Excel

Original Source Link

I am ideally looking to only turn off the drag fill series functionality.

I only want the user of my sheet to be able to drag COPY down.

I turned the feature off in settings, which disable ALL drag downs. I would be happy with that, but of course this only works in my own Excel settings, other users of the sheet will still be able too in the context of their settings.

I am wondering if there is a way in VBA to turnoff the fill series features or disable the drag feature entirely?

The biggest challenge you will face is Macro Security on the individual machines. If they are not set to run Macros this will keep this from running.

You can use the following in VBA to disable Cell Drag and Drop when the workbook opens.

Private Sub Workbook_Open()
Application.CellDragAndDrop = False
End Sub

In order to avoid making people angry, you should turn it back on when the workbook closes.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CellDragAndDrop = True
End Sub

You can use the IF statement to check if the feature is turned on off/on to ensure you return it to the original state. The biggest challenge you will face is Macro Security on the individual machines. If they are not set to run Macros this will keep this from running.

Tagged : /

Code Bug Fix: Extract mail comments – cannot find method

Original Source Link

I am creating a project to extract comments from mail messages in Outlook, however I can not find the method to access these.

Would you have any idea of how to do this?

Column and content to be extracted
Column and content to be extracted

Try to extract the PR_COMMENT_W property. The DASL name is http://schemas.microsoft.com/mapi/proptag/0x3004001F. It should be accessed through the following method:

MailItem.PropertyAccessor.GetProperty 

Tagged : /