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

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

Select allOpen in new window

Saurabh...

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)

Select allOpen in new window

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

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

Select allOpen in new window

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

Select allOpen 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

Select allOpen 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

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)

Select allOpen 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)

Select allOpen in new window

Taking this, I set the CommRange right away as

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

Select allOpen 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

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

Select allOpen 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

Select allOpen in new window

Can you provide a workbook with your code in.

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

Select allOpen 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

Select allOpen 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

Select allOpen 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

I'm not sure whether you are asking a further question or if this question is resolved.

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

Select allOpen 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...

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

Does this select the range that you want
Test1.xlsm

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)

Select allOpen 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)

Select allOpen 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..

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

Select allOpen in new window

I tried to put the above in a For Each.. Next loop to read all columns, still experimenting..

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

Select allOpen 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??

Can you attacha workbook example

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

Select allOpen 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

Select allOpen 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

Select allOpen 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!

Code that really contribued to solution was from Roy_Cox but I wanted to thank everybody for their kind contribution.
Thank you !

Thank you

Glade you managed to get it sorted.