| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range empty?
Hi There,
How comes Range = Nothing? I would like the message box to show something like: F125 Thanks, Sige Sub Show_Usedrange() Dim lngLastRow As Long Dim rLastRowCell As Range On Error Resume Next lngLastRow = 1 With ActiveSheet.UsedRange lngLastRow = .Find("*", .Cells(1), xlFormulas, xlWhole, xlByRows, xlPrevious).Row Set rLastRowCell = Cells(lngLastRow + 1, 6) MsgBox rLastRowCell End With End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range empty?
Hi Sige,
Change: MsgBox rLastRowCell to: MsgBox rLastRowCell.Address --- Regards, Norman "Sige" wrote in message oups.com... Hi There, How comes Range = Nothing? I would like the message box to show something like: F125 Thanks, Sige Sub Show_Usedrange() Dim lngLastRow As Long Dim rLastRowCell As Range On Error Resume Next lngLastRow = 1 With ActiveSheet.UsedRange lngLastRow = .Find("*", .Cells(1), xlFormulas, xlWhole, xlByRows, xlPrevious).Row Set rLastRowCell = Cells(lngLastRow + 1, 6) MsgBox rLastRowCell End With End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range empty?
Thx Norman!!!
PS: Were you able to trap the constants After the "*"-sign in my previous thread? "NOSPAM" to be removed for direct mailing... *** Sent via Developersdex http://www.developersdex.com *** |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range empty?
Hi Sige,
PS: Were you able to trap the constants After the "*"-sign in my previous thread? Yes see my reponse in that thread and the conversation with Jan Karel Pieterse: In summary: Change: arr = Array("/", "~*", "+", "-", "", "<", "=", "^") to: arr = Array("/", "~*", "+", "-", "", "<", "=", "^", "[*]", "(") --- Regards, Norman "SIGE" wrote in message ... Thx Norman!!! PS: Were you able to trap the constants After the "*"-sign in my previous thread? "NOSPAM" to be removed for direct mailing... *** Sent via Developersdex http://www.developersdex.com *** |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range empty?
Thx Norman!!!
PS: Were you able to trap the constants After the "*"-sign in my previous thread? "NOSPAM" to be removed for direct mailing... *** Sent via Developersdex http://www.developersdex.com *** |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range empty?
Hi Sige,
Yes - see response in that thread. --- Regards, Norman "SIGE" wrote in message ... Thx Norman!!! PS: Were you able to trap the constants After the "*"-sign in my previous thread? "NOSPAM" to be removed for direct mailing... *** Sent via Developersdex http://www.developersdex.com *** |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range empty?
Any reason why then
.Cells(lngLastRow + 1, 15).Formula = "=IF(" & rLastRowCell.Address(0, 0) & "="";"";$D$1)" Returns empty? Brgds, Sige Sub Check_Usedrange() Dim lngLastRow As Long, lngLastCol As Long, j As Long Dim rLastRowCell As Range On Error Resume Next lngLastRow = 1 With ActiveSheet.UsedRange lngLastRow = .Find("*", .Cells(1), xlFormulas, xlWhole, xlByRows, xlPrevious).Row Set rLastRowCell = Cells(lngLastRow + 1, 6) MsgBox rLastRowCell.Address .Rows(lngLastRow).Copy .Rows(lngLastRow + 1).PasteSpecial Paste:=xlPasteFormats .Rows(lngLastRow + 1).PasteSpecial Paste:=xlPasteFormulas For j = 1 To .Cells(lngLastRow + 1, Columns.Count).End(xlToLeft).Column If Not .Cells(lngLastRow + 1, j).HasFormula Then .Cells(lngLastRow + 1, j).ClearContents End If Next j .Cells(lngLastRow + 1, 15).Formula = "=IF(" & rLastRowCell.Address(0, 0) & "="";"";$D$1)" End With End Sub "NOSPAM" to be removed for direct mailing... *** Sent via Developersdex http://www.developersdex.com *** |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range empty?
Hi Sige,
Try: ..Cells(lngLastRow + 1, 15).Formula = "=IF(" & rLastRowCell. _ Address(0, 0) & "="""";"""";$D$1)" Note the doubled apostrophes. --- Regards, Norman "SIGE" wrote in message .. . Any reason why then Cells(lngLastRow + 1, 15).Formula = "=IF(" & rLastRowCell.Address(0, 0) & "="";"";$D$1)" Returns empty? Brgds, Sige Sub Check_Usedrange() Dim lngLastRow As Long, lngLastCol As Long, j As Long Dim rLastRowCell As Range On Error Resume Next lngLastRow = 1 With ActiveSheet.UsedRange lngLastRow = .Find("*", .Cells(1), xlFormulas, xlWhole, xlByRows, xlPrevious).Row Set rLastRowCell = Cells(lngLastRow + 1, 6) MsgBox rLastRowCell.Address Rows(lngLastRow).Copy .Rows(lngLastRow + 1).PasteSpecial Paste:=xlPasteFormats .Rows(lngLastRow + 1).PasteSpecial Paste:=xlPasteFormulas For j = 1 To .Cells(lngLastRow + 1, Columns.Count).End(xlToLeft).Column If Not .Cells(lngLastRow + 1, j).HasFormula Then .Cells(lngLastRow + 1, j).ClearContents End If Next j .Cells(lngLastRow + 1, 15).Formula = "=IF(" & rLastRowCell.Address(0, 0) & "="";"";$D$1)" End With End Sub "NOSPAM" to be removed for direct mailing... *** Sent via Developersdex http://www.developersdex.com *** |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range empty?
Hi Norman,
You are my today's hero ... !!! Though this one does not write the formula... It beats me. Sige |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range empty?
Hi Siege,
Please enter the formula in the worksheet manually and copy the resultant formula into a reply. --- Regards, Norman "Sige" wrote in message ups.com... Hi Norman, You are my today's hero ... !!! Though this one does not write the formula... It beats me. Sige |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| empty autofiltered range | Excel Programming | |||
| why is range empty? | Excel Discussion (Misc queries) | |||
| why is range empty? | Excel Programming | |||
| sum next two non-empty cells in a range | Excel Worksheet Functions | |||
| determinate if a range is empty | Excel Programming | |||
Hybrid Mode
