Excel 2010 Select range without top row (NOT using loop)

I have in Excel sheet "Data" 11 rows and 12 columns of data. First row is headers of columns, and they all have comments.
Comments are not allowed on any other cell (consider it given).

I try to right row by row and column by column all data in another sheet "Log" in same workbook. BUT I don't want the first row (the headers). I tried lots of things but don't work. I DO NOT WANT TO USE LOOPS please.

Code that doesn't work is commented below. My small Sub follows:

Option Explicit

    Dim cmt As Comment      'comment
    Dim ws As Worksheet     'working sheet
    Dim wsLog As Worksheet  'log sheet
    Dim CommRange As Range  'comments range


Public Sub ReadValues()      'reads values of rows

    Dim Rng As Range
    Dim i As Integer
    Dim cC As Integer   'character count
    
    Set CommRange = ws.Cells.SpecialCells(xlCellTypeConstants)
    
    'Set CommRange = Rng.Offset(1).Resize(Rng.Rows.Count - 1)
    
    i = wsLog.Cells(Rows.Count, 1).End(xlUp).Row + 1
    
    If Not CommRange Is Nothing Then
        For Each Rng In CommRange
            If Application.IsText(Rng) Then
                cC = Rng.Characters.Count
            End If
            wsLog.Cells(i, 1).Resize(1, 7).Value = _
            Array(ws.Name, Rng.Address(ReferenceStyle:=xlR1C1), Formatter(Rng.Text), "", "", cC, Rng.Cells.Text)
            i = i + 1
        Next
    End If
    
End Sub

Open in new window


I tried to exclude with Intersect the following:

ws.Cells.SpecialCells(xlCellTypeComments)

Open in new window


but didn't work :(

All help would be appreciated (kindly NO LOOPS For i.. Next etc) Thank you!
N MConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
You can simple use this to do what you are looking for..

Assuming row-1 has headers..

Sub copydata()
    Dim ws As Worksheet
    Dim ws1 As Worksheet
    Dim lrow As Long

    Set ws = Sheets("Data")
    Set ws1 = Sheets("Log")

    lrow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    ws.Range("A2:L" & lrow).Copy ws1.Range("A1")
End Sub

Open in new window


Saurabh...
Roy CoxGroup Finance ManagerCommented:
You can use CurrentRegion with Offset, e.g where the data starts in A1 as a header row

Set rng=ws.Cells(1,1).CurrentRegion.Offset(1)

Open in new window

N MConsultantAuthor Commented:
Saurabh : very good comment, my note: I have explicitly used SpecialCells because the user moves the columns here and there.
You may assume safely that the first row is the only one with comments, but you may not assume that first row is No 1 (A1, B1, C1... etc) It could be as well that the user added top rows for whatever reason and the selection is for example, rows 4 to 20 etc.

Roy_Cox: as per above, good contribution and thank you very much.

To all: how can we use the CommRange (currently set as ws.Cells.SpecialCells(xlCellTypeConstants) ) as a base for our selection, and from *that* range exclude the very first row (without assuming that is the row No 1 in the sheet) ?

Thank you very much both, I appreciate your time in this..
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Learn More!
Saurabh Singh TeotiaCommented:
mechanism,

Can i use any other logic to determine the first row of the data? If you can help me decipher the logic i can write a code for you to do the same..

Also alternatively.. you do something like this and then delete the header row post pasting it...i mean this...
    Dim ws As Worksheet
    Dim ws1 As Worksheet
    Dim lrow As Long
    Dim rng As Range
    Set ws = Sheets("Data")
    Set ws1 = Sheets("Log")
    Set rng = ws.Cells.SpecialCells(xlCellTypeConstants)

    rng.Copy ws1.Range("A1")
    ws1.Rows("1:1").Delete

Open in new window

Roy CoxGroup Finance ManagerCommented:
Firstly, please note that using SpecialCells can give an error if no cells match the criteria.. So add an error handler

On Error Resume Next
ws.Cells.SpecialCells(xlCellTypeComments)
On Error GoTo 0

Open in new window


Or

Option Explicit


Dim cmt As Comment      'comment
Dim ws As Worksheet     'working sheet
Dim wsLog As Worksheet  'log sheet
Dim CommRange As Range  'comments range


Public Sub ReadValues()      'reads values of rows

    Dim Rng As Range
    Dim i As Integer
    Dim cC As Integer   'character count

    Set ws = ActiveSheet
    With ws
        On Error GoTo err_quit
        Set CommRange = .Cells.SpecialCells(xlCellTypeComments)
        On Error GoTo 0
        If Not CommRange Is Nothing Then Set CommRange = CommRange.Cells(1).CurrentRegion.Offset(1)
        MsgBox CommRange.Address
    End With
    Exit Sub
err_quit:
    MsgBox "No cells were found", vbCritical, "Quitting"
End Sub

Open in new window


I don't think Constants is going to work.

To make this work more simply , can you add a hidden row above the comments row?
Test1.xlsm
N MConsultantAuthor Commented:
Hello both for your valuable contribution.

Saurabh: The range is some rows in a worksheet. Now, we are able to select this range of cells, but the first row should be excluded. I wanted to exclude it (and this is how I distinguish this row) as all cells having comments (only the first row, full row, has comments; no other cell in spreadsheet; but I don't know where is that row).
So, the distinction is by separating the cells with comments, and this subrange is all I have:

Set ExcludedRange = ws.Cells.SpecialCells(xlCellTypeComments)

Open in new window


To Roy_Cox

Brilliant, it works (thank you for the comment on error handling, I will follow your advice)
The interesting line in your code is this:

If Not CommRange Is Nothing Then Set CommRange = CommRange.Cells(1).CurrentRegion.Offset(1)

Open in new window


Taking this, I set the CommRange right away as 

Set CommRange = ws.Cells.SpecialCells(xlCellTypeConstants).Cells(1).CurrentRegion.Offset(1)

Open in new window


And it worked; now, an interesting side effect: although by using SpecialCells(xlCellTypeConstants) gave me the advantage to have also all non-blank cells, by using this line of code above when I read the array of lines, cells with no content are included. Is there any way I can solve this? (Note: might be MY mistake, so in next comment I give you the working code I have now)

THANK YOU all
N MConsultantAuthor Commented:
I provide herein the code after Roy_Cox paradigm. The only thing is that empty cells (blank cells) are now included.

Class generals:

Option Explicit

    Dim cmt As Comment      'comment
    Dim ws As Worksheet     'working sheet
    Dim wsLog As Worksheet  'log sheet
    Dim CommRange As Range  'comments range

Private Sub Class_Initialize()  'object create
    
    Set ws = Sheets("Data")
    Set wsLog = Sheets("Log")
    Set CommRange = Nothing

End Sub

Private Sub Class_Terminate()   'house cleaning

    Set wsLog = Nothing
    Set ws = Nothing
    Set CommRange = Nothing

End Sub

Open in new window


And the working Sub as per Roy_Cox amendment:

Public Sub ReadValues()      'reads values of rows

    Dim Rng As Range
    Dim i As Integer
    Dim cC As Integer   'character count

    Set CommRange = ws.Cells.SpecialCells(xlCellTypeConstants).Cells(1).CurrentRegion.Offset(1)
    
    If Not CommRange Is Nothing Then
        
        i = wsLog.Cells(Rows.Count, 1).End(xlUp).Row + 1
        
        For Each Rng In CommRange
            If Application.IsText(Rng) Then
                cC = Rng.Characters.Count
            End If
            wsLog.Cells(i, 1).Resize(1, 7).Value = _
            Array(ws.Name, Rng.Address(ReferenceStyle:=xlR1C1), Rng.Text, "", "", cC, Rng.Cells.Text)
            i = i + 1
        Next
    
    End If
    
End Sub

Open in new window

Roy CoxGroup Finance ManagerCommented:
Can you provide a workbook with your code in.
N MConsultantAuthor Commented:
I wil try to provide XL, in the mean time, these are the data in Sheet "DATA" (copy below in empty notepad file and save as .csv and Excel will read it)

Message	Reference	Company Name	Company Name	Company Address	Other Info	Message Text Info	Customer	Account	Currency	Balance in original currency	Balance in CHF
M1	R1	CompanyName	OtherName	"Somestreet 1, New York"	CS UH2	Local	345612	80012354	USD	10000	10127.25
M1	R2	CompanyName	OtherName	"Somestreet 1, New York"	CS UH2	Local	345612	80012368	CHF	95640.7	95640.7
M1	R3	CompanyName	OtherName	"Somestreet 1, New York"	CS UH2	Local	345612	80012394	GBP	143000	211107.75
M1	R4	CompanyName		"Somestreet 1, New York"	CS UH2	Local	425078	80024301	CHF	85000	85000
M1	R5	CompanyName		"Somestreet 1, New York"	CS UH2	Local	425078	80025961	USD	12000	12152.7
M1	R6	CompanyName		"Somestreet 1, New York"	CS UH2	Mandate	428640	800168001	USD	695000	703843.88
M1	R7	CompanyName		"Somestreet 1, New York"	CS UH1	Local	354016	80025601	CHF	120000	120000
M1	R8	CompanyName		"Somestreet 1, New York"	CS UH1	Local	354016	80025646	CHF	52945.58	52945.58
M1	R9	CompanyName		"Somestreet 1, New York"	CS UH2	Mandate	428751	800168215	USD	425000	430408.13
M1	R10	CompanyName		"Somestreet 1, New York"	CS UH1	Local	377677	80031255	CHF	120000	120000

Open in new window


Now, the code is all about a class called "Process" and a module who simply calls it. Here the class:

Option Explicit

    Dim cmt As Comment      'comment
    Dim ws As Worksheet     'working sheet
    Dim wsLog As Worksheet  'log sheet
    Dim CommRange As Range  'comments range

Private Sub Class_Initialize()  'object create
    
    Set ws = Sheets("Data")
    Set wsLog = Sheets("Log")
    Set CommRange = Nothing

End Sub

Private Sub Class_Terminate()   'house cleaning

    Set wsLog = Nothing
    Set ws = Nothing
    Set CommRange = Nothing

End Sub

Public Sub ReadValues()      'reads values of rows

    Dim Rng As Range
    Dim i As Integer
    Dim cC As Integer   'character count

    Set CommRange = ws.Cells.SpecialCells(xlCellTypeConstants).Cells(1).CurrentRegion.Offset(1)
    
    If Not CommRange Is Nothing Then
        
        i = wsLog.Cells(Rows.Count, 1).End(xlUp).Row + 1
        
        For Each Rng In CommRange
            If Application.IsText(Rng) Then
                cC = Rng.Characters.Count
            End If
            wsLog.Cells(i, 1).Resize(1, 7).Value = _
            Array(ws.Name, Rng.Address(ReferenceStyle:=xlR1C1), Formatter(Rng.Text), "", "", cC, Rng.Cells.Text)
            i = i + 1
        Next
    
    End If
    
    FormatLog
    
End Sub

Public Sub ReadHeaders()    'reads comments from top of columns to build the XML headers

    Dim i As Integer
    Dim Rng As Range
    
        Application.ScreenUpdating = False
    
    Set CommRange = ws.Cells.SpecialCells(xlCellTypeComments)
    
    With wsLog
        If .Cells(Rows.Count, 1).End(xlUp).Row = 1 Then
            .Cells(1, 1).Value = "Date Time / Source"
            .Cells(1, 2).Value = "Address"
            .Cells(1, 3).Value = "Data Column"
            .Cells(1, 4).Value = "XML Element"
            .Cells(1, 5).Value = "Required"
            .Cells(1, 6).Value = "Length"
            .Cells(1, 7).Value = "Data"
        End If
        
        i = .Cells(Rows.Count, 1).End(xlUp).Row + 1
        
        .Cells(i, 1).Value = Format(CStr(Now), "yyyy-mm-dd_hh:mm:ss")
            
            If Not CommRange Is Nothing Then
                For Each Rng In CommRange
                    i = i + 1
                    .Cells(i, 1).Resize(1, 5).Value = _
                    Array(ws.Name, Rng.Address(ReferenceStyle:=xlR1C1), Rng.Value, Formatter(Rng.Comment.Text), Rng.Comment.Shape.TextFrame.Characters.Font.Bold)
                Next
            End If
        
        Set CommRange = Nothing
        FormatLog
    
    End With
    
        Application.ScreenUpdating = True

End Sub

Private Function Formatter(ByVal varVal As Variant) 'formats the comment text before log writing

    Dim NewVal As Variant
    
    If IsMissing(varVal) Then
        Exit Function
    End If
    
        NewVal = Trim(varVal) 'remove spaces

        With Application.WorksheetFunction
            NewVal = .Clean(NewVal) 'remove most unwanted characters
            NewVal = .Substitute(NewVal, Chr(10), "")   'remove carriage return
            NewVal = .Substitute(NewVal, Chr(13), "")   'remove line feed
            NewVal = .Substitute(NewVal, Chr(127), "")  'remove ASCII#127
            NewVal = .Substitute(NewVal, Chr(160), "")  'remove ASCII#160
        End With
    
    Formatter = "'" & NewVal

End Function

Private Function FormatLog()
    
    With wsLog.Cells                                'log sheet format
        .ClearFormats
        .HorizontalAlignment = xlLeft
        .Font.Name = "Courier New"
        .Font.Size = 10
        .Font.FontStyle = "Regular"
        .Font.Color = vbBlack
        .Interior.ColorIndex = xlColorIndexNone     '.Interior.ColorIndex = 120
        .Borders.LineStyle = xlNone
        .Rows.RowHeight = 13.2
        .EntireColumn.AutoFit
    End With

End Function

Open in new window


And the module called "Messaging" - you name it as you want - who simply calls the class, nothing fancy here:

Option Explicit

Public Sub Process_Read()

    Dim Process As New Process
    
    Process.ReadHeaders
    Process.ReadValues

End Sub

Open in new window


NOTE:
1. Put some comments in first row cells, whatever comments that might be.
2. Just make sure that you also have an empty sheet called "LOG" and then you may just place a button on sheet "DATA" with whatever caption and right-click on it and assign macro "Process_Read".
Pressing the button, assuming you have copied the data in "DATA" sheet, will populate the "LOG" sheet.



I hope these help
Roy CoxGroup Finance ManagerCommented:
I'm not sure whether you are asking a further question or if this question is resolved.
N MConsultantAuthor Commented:
Hi Roy_Cox

No I am not asking a new question.
The ws.Cells.SpecialCells(xlCellTypeConstants) is only selecting the non-empty cells as per default functionality.

You can verify this by running the code below instead of the Set CommRange... line :

    Set CommRange = ws.Cells.SpecialCells(xlCellTypeConstants)
    CommRange.Select
    Stop

Open in new window

You will notice the non-empty cells are not being selected - just the ones the SpecialCells are being selecting.
I can accept the solution provided if there is no way we can turn this around, because now in LOG sheet also empty cells are been written.
I have been trying Intersect but with no success...
Saurabh Singh TeotiaCommented:
mechanism,

If you use this feature their is no way that you can select blank cells  as the above will only select filled cells only..Either you need to switch over to usedrange method or some other method to select your complete range what you are looking for..

Saurabh...
Roy CoxGroup Finance ManagerCommented:
Does this select the range that you want
Test1.xlsm
N MConsultantAuthor Commented:
Hi Singh Teotia - there must be a misunderstanding in your answer: I do NOT want the blank cells.
That's why I used SpecialCells method.

The only problem that still resides in the answer by Roy_Cox is that, while this original code:

Set CommRange = ws.Cells.SpecialCells(xlCellTypeConstants)

Open in new window

gets only the full cells leaving the blank outs, while we try to deselect the first row (Roy_Cox solution)

Set CommRange = ws.Cells.SpecialCells(xlCellTypeConstants).Cells(1).CurrentRegion.Offset(1)

Open in new window

we get also the blank cells.

If we can't get around of this, i.e. using Resize or something (I'm also working on it), we will have to accept it. But there must be a solution in it..
Roy CoxGroup Finance ManagerCommented:
Did you try my last attachment? The code in that only selects non-blank cells in CommRange

Option Explicit


Dim cmt As Comment      'comment
Dim ws As Worksheet     'working sheet
Dim wsLog As Worksheet  'log sheet
Dim CommRange As Range  'comments range


Public Sub ReadValues()      'reads values of rows

    Dim Rng As Range
    Dim i As Integer
    Dim cC As Integer   'character count

    Set ws = ActiveSheet
    With ws
        On Error GoTo err_quit
        Set CommRange = .Cells.SpecialCells(xlCellTypeComments)
        On Error GoTo 0
        If Not CommRange Is Nothing Then Set CommRange = Range(CommRange.Cells(1), Cells(Rows.Count, 2).End(xlUp)).SpecialCells(xlCellTypeConstants)
''/// added to test
        MsgBox CommRange.Address
        CommRange.Select
    End With
    Exit Sub
err_quit:
    MsgBox "No cells were found", vbCritical, "Quitting"
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
N MConsultantAuthor Commented:
To Roy_Cox

YES !! Indeed the code makes the selection correct.
But just runs for the first column ! Can we updated to select all columns?

This is my data (first row all cells have comments):

Message	Reference	Company Name	Company Name	Company Address	Other Info	Message Text Info	Customer	Account	Currency	Balance in original currency	Balance in CHF
M1	R1	CompanyName	OtherName	Somestreet 1, New York	CS UH2	Local	345612	80012354	USD	10000	10127.25
M1	R2	CompanyName	OtherName	Somestreet 1, New York	CS UH2	Local	345612	80012368	CHF	95640.7	95640.7
M1	R3	CompanyName	OtherName	Somestreet 1, New York	CS UH2	Local	345612	80012394	GBP	143000	211107.75
M1	R4	CompanyName		Somestreet 1, New York	CS UH2	Local	425078	80024301	CHF	85000	85000
M1	R5	CompanyName		Somestreet 1, New York	CS UH2	Local	425078	80025961	USD	12000	12152.7
M1	R6	CompanyName		Somestreet 1, New York	CS UH2	Mandate	428640	800168001	USD	695000	703843.88
M1	R7	CompanyName		Somestreet 1, New York	CS UH1	Local	354016	80025601	CHF	120000	120000
M1	R8	CompanyName		Somestreet 1, New York	CS UH1	Local	354016	80025646	CHF	52945.58	52945.58
M1	R9	CompanyName		Somestreet 1, New York	CS UH2	Mandate	428751	800168215	USD	425000	430408.13
M1	R10	CompanyName		Somestreet 1, New York	CS UH1	Local	377677	80031255	CHF	120000	120000

Open in new window

N MConsultantAuthor Commented:
I tried to put the above in a For Each.. Next loop to read all columns, still experimenting..
N MConsultantAuthor Commented:
To Roy_Cox

Your answer is the main contributor to the solution; I updated my code in line with yours, including the error control, and now is like this:

Public Sub ReadValues()      'reads values of rows

    Dim Rng As Range
    Dim i As Integer
    Dim cC As Integer   'character count

    On Error GoTo sQuit
        Application.ScreenUpdating = False
    
    Set CommRange = ws.Cells.SpecialCells(xlCellTypeComments)
    On Error GoTo 0
        
    If Not CommRange Is Nothing Then
        
        Set CommRange = Range(CommRange.Cells(1), Cells(Rows.Count, 2).End(xlUp)).SpecialCells(xlCellTypeConstants)
    
        i = wsLog.Cells(Rows.Count, 1).End(xlUp).Row + 1
        
        For Each Rng In CommRange
            If Application.IsText(Rng) Then
                cC = Rng.Characters.Count
            End If
            wsLog.Cells(i, 1).Resize(1, 7).Value = _
            Array(ws.Name, Rng.Address(ReferenceStyle:=xlR1C1), Formatter(Rng.Text), "###", "###", cC, Rng.Cells.Text)
            i = i + 1
        Next
    
    End If
    
    FormatLog

sQuit:
    Application.ScreenUpdating = True
End Sub

Open in new window


It is really a breath away from completion, I only ask for this advice: it is selecting first column (correctly excluding all non-blank cells) and all rows (correct).
How can I make it go through all columns??
Roy CoxGroup Finance ManagerCommented:
Can you attacha workbook example
N MConsultantAuthor Commented:
This is the current status, and as it seems kudos go to Roy_Cox
I modified the code to his reply (have difficulty to upload workbook apologies, but I give all data).

This is the data on sheet "DATA" (in pipe-separated format, copy/paste as filename.txt and load "DATA" sheet)

Message|Reference|Company Name|Company Name|Company Address|Other Info|Message Text Info|Customer|Account|Currency|Balance in original currency|Balance in CHF
M1|R1|CompanyName|OtherName|"Somestreet 1, New York"|CS UH2|Local|345612|80012354|USD|10000|10127.25
M1|R2|CompanyName|OtherName|"Somestreet 1, New York"|CS UH2|Local|345612|80012368|CHF|95640.7|95640.7
M1|R3|CompanyName|OtherName|"Somestreet 1, New York"|CS UH2|Local|345612|80012394|GBP|143000|211107.75
M1|R4|CompanyName||"Somestreet 1, New York"|CS UH2|Local|425078|80024301|CHF|85000|85000
||CompanyName||"Somestreet 1, New York"|CS UH2|Local|425078|80025961|USD|12000|12152.7
M1|R6|CompanyName||"Somestreet 1, New York"|CS UH2|Mandate|428640|800168001|USD|695000|703843.88
M1|R7|CompanyName||"Somestreet 1, New York"|CS UH1|Local|354016|80025601|CHF|120000|120000
M1|R8|CompanyName||"Somestreet 1, New York"|CS UH1|Local|354016|80025646|CHF|52945.58|52945.58
M1|R9|CompanyName||"Somestreet 1, New York"|CS UH2|Mandate|428751|800168215|USD|425000|430408.13
M1|R10|CompanyName||"Somestreet 1, New York"|CS UH1|Local|377677|80031255|CHF|120000|120000

Open in new window



And this is the Class with the code (I provide all code)
Option Explicit

    Dim cmt As Comment      'comment
    Dim ws As Worksheet     'working sheet
    Dim wsLog As Worksheet  'log sheet
    Dim CommRange As Range  'comments range


Public Sub ReadValues()      'reads values of rows

    Dim Rng As Range
    Dim i As Integer
    Dim cC As Integer   'character count

    On Error GoTo sQuit
        Application.ScreenUpdating = False
    
    Set CommRange = ws.Cells.SpecialCells(xlCellTypeComments)
    On Error GoTo 0
        
    If Not CommRange Is Nothing Then
         
        'need confirmation
        Set CommRange = Range(CommRange.Cells(2, Columns.Count), Cells(Rows.Count, 1).End(xlUp)).SpecialCells(xlCellTypeConstants)
    
        i = wsLog.Cells(Rows.Count, 1).End(xlUp).Row + 1
        
        For Each Rng In CommRange
            If Application.IsText(Rng) Then
                cC = Rng.Characters.Count
            End If
            wsLog.Cells(i, 1).Resize(1, 7).Value = _
            Array(ws.Name, Rng.Address(ReferenceStyle:=xlR1C1), Formatter(Rng.Text), "###", "###", cC, Rng.Cells.Text)
            i = i + 1
        Next
    
    End If
    
    FormatLog

sQuit:
    Application.ScreenUpdating = True
End Sub

Public Sub ReadHeaders()    'reads comments from top of columns to build the XML headers

    Dim i As Integer
    Dim Rng As Range
    
    On Error GoTo sQuit
        Application.ScreenUpdating = False
    
    Set CommRange = ws.Cells.SpecialCells(xlCellTypeComments)
    
    With wsLog
        If .Cells(Rows.Count, 1).End(xlUp).Row = 1 Then
            .Cells(1, 1).Value = "Date Time / Source"
            .Cells(1, 2).Value = "Address"
            .Cells(1, 3).Value = "Data Column"
            .Cells(1, 4).Value = "XML Element"
            .Cells(1, 5).Value = "Required"
            .Cells(1, 6).Value = "Length"
            .Cells(1, 7).Value = "Data"
        End If
        
        i = .Cells(Rows.Count, 1).End(xlUp).Row + 1
        
        .Cells(i, 1).Value = Format(CStr(Now), "yyyy-mm-dd_hh:mm:ss")
            
            If Not CommRange Is Nothing Then
                For Each Rng In CommRange
                    i = i + 1
                    .Cells(i, 1).Resize(1, 5).Value = _
                    Array(ws.Name, Rng.Address(ReferenceStyle:=xlR1C1), Rng.Value, Formatter(Rng.Comment.Text), Rng.Comment.Shape.TextFrame.Characters.Font.Bold)
                Next
            End If
        
        Set CommRange = Nothing
        FormatLog
    
    End With
    
        Application.ScreenUpdating = True
sQuit:
    Application.ScreenUpdating = True
End Sub




''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''       Private Functions                                                                    ''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Private Sub Class_Initialize()  'object create
    
    Set ws = Sheets("Data")
    Set wsLog = Sheets("Log")
    Set CommRange = Nothing

End Sub

Private Sub Class_Terminate()   'house cleaning

    Set wsLog = Nothing
    Set ws = Nothing
    Set CommRange = Nothing

End Sub

Private Function Formatter(ByVal varVal As Variant) 'formats the comment text before log writing

    Dim NewVal As Variant
    
    If IsMissing(varVal) Or Len(varVal) < 1 Then
    Get_Variable_Type (varVal)
        Exit Function
    End If
    
        NewVal = Trim(varVal) 'remove spaces

        With Application.WorksheetFunction
            NewVal = .Clean(NewVal) 'remove most unwanted characters
            NewVal = .Substitute(NewVal, Chr(10), "")   'remove carriage return
            NewVal = .Substitute(NewVal, Chr(13), "")   'remove line feed
            NewVal = .Substitute(NewVal, Chr(127), "")  'remove ASCII#127
            NewVal = .Substitute(NewVal, Chr(160), "")  'remove ASCII#160
        End With
    
    Formatter = "'" & NewVal

End Function

Private Function FormatLog()
    
    With wsLog.Cells                                'log sheet format
        .ClearFormats
        .HorizontalAlignment = xlLeft
        .Font.Name = "Courier New"
        .Font.Size = 10
        .Font.FontStyle = "Regular"
        .Font.Color = vbBlack
        .Interior.ColorIndex = xlColorIndexNone     '.Interior.ColorIndex = 120
        .Borders.LineStyle = xlNone
        .Rows.RowHeight = 13.2
        .EntireColumn.AutoFit
    End With

End Function

Open in new window


Just add a completely empty "LOG" sheet and run the below in a macro:

Public Sub Process_Read()

    Dim Process As New Process
    
    Process.ReadHeaders
    
    Process.ReadValues

End Sub

Open in new window


As it seems, it works (if you like confirm) - the code we are focusing on is just the ReadValues sub.

Thank you!
N MConsultantAuthor Commented:
Code that really contribued to solution was from Roy_Cox but I wanted to thank everybody for their kind contribution.
Thank you !
N MConsultantAuthor Commented:
Thank you
Roy CoxGroup Finance ManagerCommented:
Glade you managed to get it sorted.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.