| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Including blank rows in a series of comments (sugarthebeet)
I would like to exclude all comments except those in column G and include
blank cells too. Can someone help me tweak this code? Sub PrintCommentsByColumn() Dim cell As Range Dim myrange As Range, myrangeC As Range Dim col As Long Dim RowOS As Long Dim wsSource As Worksheet Dim wsNew As Worksheet If ActiveSheet.Comments.Count = 0 Then MsgBox "No comments in entire sheet" Exit Sub End If Application.ScreenUpdating = False Application.Calculation = xlCalculationManual 'xl95 uses xlManual Set wsSource = ActiveSheet Sheets.Add Set wsNew = ActiveSheet wsSource.Activate With wsNew.Columns("A:C") .VerticalAlignment = xlTop .WrapText = True End With wsNew.Columns("B").ColumnWidth = 15 wsNew.Columns("C").ColumnWidth = 60 wsNew.PageSetup.PrintGridlines = True RowOS = 2 wsNew.Cells(1, 3) = "'" & Application.ActiveWorkbook.FullName & " -- " & _ Application.ActiveSheet.Name For col = 1 To ActiveSheet.UsedRange.Columns.Count Set myrangeC = Intersect(ActiveSheet.UsedRange, Columns(col), _ Cells.SpecialCells(xlCellTypeComments)) If myrangeC Is Nothing Then GoTo nxtCol For Each cell In myrangeC If Trim(cell.Comment.Text) < "" Then RowOS = RowOS + 1 wsNew.Cells(RowOS, 1) = "'" & cell.Address(0, 0) & ":" wsNew.Cells(RowOS, 2) = "'" & cell.Text wsNew.Cells(RowOS, 3) = "'" & cell.Comment.Text End If Next cell nxtCol: Next col wsNew.Activate Application.Calculation = xlCalculationAutomatic 'xl95 uses xlAutomatic Application.ScreenUpdating = True End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Including blank rows in a series of comments (sugarthebeet)
Sub PrintCommentsByColumn()
Dim cell As Range Dim myrange As Range, myrangeC As Range Dim col As Long Dim RowOS As Long Dim wsSource As Worksheet Dim wsNew As Worksheet If ActiveSheet.Comments.Count = 0 Then MsgBox "No comments in entire sheet" Exit Sub End If Application.ScreenUpdating = False Application.Calculation = xlCalculationManual 'xl95 uses xlManual Set wsSource = ActiveSheet Sheets.Add Set wsNew = ActiveSheet wsSource.Activate With wsNew.Columns("A:C") .VerticalAlignment = xlTop .WrapText = True End With wsNew.Columns("B").ColumnWidth = 15 wsNew.Columns("C").ColumnWidth = 60 wsNew.PageSetup.PrintGridlines = True RowOS = 2 wsNew.Cells(1, 3) = "'" & Application.ActiveWorkbook.FullName & " -- " & _ Application.ActiveSheet.Name col = "G" On Error Resume Next Set myrangeC = Columns(col).Cells.SpecialCells(xlCellTypeComments )) On Error goto 0 If myrangeC Is Nothing Then Exit sub For Each cell In myrangeC If Trim(cell.Comment.Text) < "" Then RowOS = RowOS + 1 wsNew.Cells(RowOS, 1) = "'" & cell.Address(0, 0) & ":" wsNew.Cells(RowOS, 2) = "'" & cell.Text wsNew.Cells(RowOS, 3) = "'" & cell.Comment.Text End If Next cell wsNew.Activate Application.Calculation = xlCalculationAutomatic 'xl95 uses xlAutomatic Application.ScreenUpdating = True End Sub -- Regards, Tom Ogilvy "Sugarthebeet" wrote in message ... I would like to exclude all comments except those in column G and include blank cells too. Can someone help me tweak this code? Sub PrintCommentsByColumn() Dim cell As Range Dim myrange As Range, myrangeC As Range Dim col As Long Dim RowOS As Long Dim wsSource As Worksheet Dim wsNew As Worksheet If ActiveSheet.Comments.Count = 0 Then MsgBox "No comments in entire sheet" Exit Sub End If Application.ScreenUpdating = False Application.Calculation = xlCalculationManual 'xl95 uses xlManual Set wsSource = ActiveSheet Sheets.Add Set wsNew = ActiveSheet wsSource.Activate With wsNew.Columns("A:C") .VerticalAlignment = xlTop .WrapText = True End With wsNew.Columns("B").ColumnWidth = 15 wsNew.Columns("C").ColumnWidth = 60 wsNew.PageSetup.PrintGridlines = True RowOS = 2 wsNew.Cells(1, 3) = "'" & Application.ActiveWorkbook.FullName & " -- " & _ Application.ActiveSheet.Name For col = 1 To ActiveSheet.UsedRange.Columns.Count Set myrangeC = Intersect(ActiveSheet.UsedRange, Columns(col), _ Cells.SpecialCells(xlCellTypeComments)) If myrangeC Is Nothing Then GoTo nxtCol For Each cell In myrangeC If Trim(cell.Comment.Text) < "" Then RowOS = RowOS + 1 wsNew.Cells(RowOS, 1) = "'" & cell.Address(0, 0) & ":" wsNew.Cells(RowOS, 2) = "'" & cell.Text wsNew.Cells(RowOS, 3) = "'" & cell.Comment.Text End If Next cell nxtCol: Next col wsNew.Activate Application.Calculation = xlCalculationAutomatic 'xl95 uses xlAutomatic Application.ScreenUpdating = True End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Including blank rows in a series of comments (sugarthebeet)
Thank Tom, but the VB editor doesn't seem to like this line:
Set myrangeC = Columns(col).Cells.SpecialCells(xlCellTypeComments )) Compile Error - Syntax Error Know what to do? SB "Sugarthebeet" wrote: I would like to exclude all comments except those in column G and include blank cells too. Can someone help me tweak this code? Sub PrintCommentsByColumn() Dim cell As Range Dim myrange As Range, myrangeC As Range Dim col As Long Dim RowOS As Long Dim wsSource As Worksheet Dim wsNew As Worksheet If ActiveSheet.Comments.Count = 0 Then MsgBox "No comments in entire sheet" Exit Sub End If Application.ScreenUpdating = False Application.Calculation = xlCalculationManual 'xl95 uses xlManual Set wsSource = ActiveSheet Sheets.Add Set wsNew = ActiveSheet wsSource.Activate With wsNew.Columns("A:C") .VerticalAlignment = xlTop .WrapText = True End With wsNew.Columns("B").ColumnWidth = 15 wsNew.Columns("C").ColumnWidth = 60 wsNew.PageSetup.PrintGridlines = True RowOS = 2 wsNew.Cells(1, 3) = "'" & Application.ActiveWorkbook.FullName & " -- " & _ Application.ActiveSheet.Name For col = 1 To ActiveSheet.UsedRange.Columns.Count Set myrangeC = Intersect(ActiveSheet.UsedRange, Columns(col), _ Cells.SpecialCells(xlCellTypeComments)) If myrangeC Is Nothing Then GoTo nxtCol For Each cell In myrangeC If Trim(cell.Comment.Text) < "" Then RowOS = RowOS + 1 wsNew.Cells(RowOS, 1) = "'" & cell.Address(0, 0) & ":" wsNew.Cells(RowOS, 2) = "'" & cell.Text wsNew.Cells(RowOS, 3) = "'" & cell.Comment.Text End If Next cell nxtCol: Next col wsNew.Activate Application.Calculation = xlCalculationAutomatic 'xl95 uses xlAutomatic Application.ScreenUpdating = True End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Including blank rows in a series of comments (sugarthebeet)
I edited your code and failed to remove the extra Right paren on the end.
-- Regards, Tom Ogilvy "Sugarthebeet" wrote in message ... Thank Tom, but the VB editor doesn't seem to like this line: Set myrangeC = Columns(col).Cells.SpecialCells(xlCellTypeComments )) Compile Error - Syntax Error Know what to do? SB "Sugarthebeet" wrote: I would like to exclude all comments except those in column G and include blank cells too. Can someone help me tweak this code? Sub PrintCommentsByColumn() Dim cell As Range Dim myrange As Range, myrangeC As Range Dim col As Long Dim RowOS As Long Dim wsSource As Worksheet Dim wsNew As Worksheet If ActiveSheet.Comments.Count = 0 Then MsgBox "No comments in entire sheet" Exit Sub End If Application.ScreenUpdating = False Application.Calculation = xlCalculationManual 'xl95 uses xlManual Set wsSource = ActiveSheet Sheets.Add Set wsNew = ActiveSheet wsSource.Activate With wsNew.Columns("A:C") .VerticalAlignment = xlTop .WrapText = True End With wsNew.Columns("B").ColumnWidth = 15 wsNew.Columns("C").ColumnWidth = 60 wsNew.PageSetup.PrintGridlines = True RowOS = 2 wsNew.Cells(1, 3) = "'" & Application.ActiveWorkbook.FullName & " -- " & _ Application.ActiveSheet.Name For col = 1 To ActiveSheet.UsedRange.Columns.Count Set myrangeC = Intersect(ActiveSheet.UsedRange, Columns(col), _ Cells.SpecialCells(xlCellTypeComments)) If myrangeC Is Nothing Then GoTo nxtCol For Each cell In myrangeC If Trim(cell.Comment.Text) < "" Then RowOS = RowOS + 1 wsNew.Cells(RowOS, 1) = "'" & cell.Address(0, 0) & ":" wsNew.Cells(RowOS, 2) = "'" & cell.Text wsNew.Cells(RowOS, 3) = "'" & cell.Comment.Text End If Next cell nxtCol: Next col wsNew.Activate Application.Calculation = xlCalculationAutomatic 'xl95 uses xlAutomatic Application.ScreenUpdating = True End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Including blank rows in a series of comments (sugarthebeet)
Hi Tom,
Actually I get a runtime error '13' even after deleting that extra bracket. What do you think? "Sugarthebeet" wrote: I would like to exclude all comments except those in column G and include blank cells too. Can someone help me tweak this code? Sub PrintCommentsByColumn() Dim cell As Range Dim myrange As Range, myrangeC As Range Dim col As Long Dim RowOS As Long Dim wsSource As Worksheet Dim wsNew As Worksheet If ActiveSheet.Comments.Count = 0 Then MsgBox "No comments in entire sheet" Exit Sub End If Application.ScreenUpdating = False Application.Calculation = xlCalculationManual 'xl95 uses xlManual Set wsSource = ActiveSheet Sheets.Add Set wsNew = ActiveSheet wsSource.Activate With wsNew.Columns("A:C") .VerticalAlignment = xlTop .WrapText = True End With wsNew.Columns("B").ColumnWidth = 15 wsNew.Columns("C").ColumnWidth = 60 wsNew.PageSetup.PrintGridlines = True RowOS = 2 wsNew.Cells(1, 3) = "'" & Application.ActiveWorkbook.FullName & " -- " & _ Application.ActiveSheet.Name For col = 1 To ActiveSheet.UsedRange.Columns.Count Set myrangeC = Intersect(ActiveSheet.UsedRange, Columns(col), _ Cells.SpecialCells(xlCellTypeComments)) If myrangeC Is Nothing Then GoTo nxtCol For Each cell In myrangeC If Trim(cell.Comment.Text) < "" Then RowOS = RowOS + 1 wsNew.Cells(RowOS, 1) = "'" & cell.Address(0, 0) & ":" wsNew.Cells(RowOS, 2) = "'" & cell.Text wsNew.Cells(RowOS, 3) = "'" & cell.Comment.Text End If Next cell nxtCol: Next col wsNew.Activate Application.Calculation = xlCalculationAutomatic 'xl95 uses xlAutomatic Application.ScreenUpdating = True End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Including blank rows in a series of comments (sugarthebeet)
A type mismatch error?
So what line of code is highlighted. What are the values of the variables in that line of code when you get the error? -- Regards, Tom Ogilvy "Sugarthebeet" wrote in message ... Hi Tom, Actually I get a runtime error '13' even after deleting that extra bracket. What do you think? "Sugarthebeet" wrote: I would like to exclude all comments except those in column G and include blank cells too. Can someone help me tweak this code? Sub PrintCommentsByColumn() Dim cell As Range Dim myrange As Range, myrangeC As Range Dim col As Long Dim RowOS As Long Dim wsSource As Worksheet Dim wsNew As Worksheet If ActiveSheet.Comments.Count = 0 Then MsgBox "No comments in entire sheet" Exit Sub End If Application.ScreenUpdating = False Application.Calculation = xlCalculationManual 'xl95 uses xlManual Set wsSource = ActiveSheet Sheets.Add Set wsNew = ActiveSheet wsSource.Activate With wsNew.Columns("A:C") .VerticalAlignment = xlTop .WrapText = True End With wsNew.Columns("B").ColumnWidth = 15 wsNew.Columns("C").ColumnWidth = 60 wsNew.PageSetup.PrintGridlines = True RowOS = 2 wsNew.Cells(1, 3) = "'" & Application.ActiveWorkbook.FullName & " -- " & _ Application.ActiveSheet.Name For col = 1 To ActiveSheet.UsedRange.Columns.Count Set myrangeC = Intersect(ActiveSheet.UsedRange, Columns(col), _ Cells.SpecialCells(xlCellTypeComments)) If myrangeC Is Nothing Then GoTo nxtCol For Each cell In myrangeC If Trim(cell.Comment.Text) < "" Then RowOS = RowOS + 1 wsNew.Cells(RowOS, 1) = "'" & cell.Address(0, 0) & ":" wsNew.Cells(RowOS, 2) = "'" & cell.Text wsNew.Cells(RowOS, 3) = "'" & cell.Comment.Text End If Next cell nxtCol: Next col wsNew.Activate Application.Calculation = xlCalculationAutomatic 'xl95 uses xlAutomatic Application.ScreenUpdating = True End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dynamic Range including some blank rows | Excel Discussion (Misc queries) | |||
| Get number of rows that data uses, including blank rows | Excel Discussion (Misc queries) | |||
| Get number of rows that data takes up, including blank rows | Excel Worksheet Functions | |||
| How to pull cells from other worksheets including color/comments? | Excel Worksheet Functions | |||
| Subtotals including blank rows | Excel Discussion (Misc queries) | |||
Linear Mode
