Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding last column not always working correctly
I have a macro to find a last column in all the worksheets in a workbook. In some sheets it is correctly selecting the last column, but in some sheets it is passing the last column and selecting some other column on way right to it. I tried to use the following two methods, both are working the same way. Could someone figure out the correct way to find the last column? Public Sub test() Dim ws As Worksheet Dim Lastrow, Lastcolumn As Long On Error Resume Next For Each ws In ActiveWorkbook.Worksheets With ws Lastrow = .Range("A" & Rows.Count).End(xlUp).Row 'Lastcolumn = ActiveCell.SpecialCells(xlCellTypeLastCell).Column Lastcolumn = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column ..Cells(Lastrow, 1).Copy ..Range(.Cells(1, Lastcolumn + 1), .Cells(Lastrow, Lastcolumn + 1)).PasteSpecial Paste:=xlPasteAll, Transpose:=True End With Next ws On Error GoTo 0 End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding last column not always working correctly
You must have some formatting or some kind of 'invisible' data in those
columns that get passed over. In one of those troublesome sheets, click a cell, and hit Ctrl+End on the keyboard. Do you go beyond the column that you expect to stop at? If so, click on the letter to the right of the last column you want to stop at, Ctrl+Shift+RightArrowKey, then right-click and click Delete. Save the workbook, hit Ctrl+End, now you should stop at the column that you expect to stop at. Give it a try and post back with your findings. HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Sheela" wrote: I have a macro to find a last column in all the worksheets in a workbook. In some sheets it is correctly selecting the last column, but in some sheets it is passing the last column and selecting some other column on way right to it. I tried to use the following two methods, both are working the same way. Could someone figure out the correct way to find the last column? Public Sub test() Dim ws As Worksheet Dim Lastrow, Lastcolumn As Long On Error Resume Next For Each ws In ActiveWorkbook.Worksheets With ws Lastrow = .Range("A" & Rows.Count).End(xlUp).Row 'Lastcolumn = ActiveCell.SpecialCells(xlCellTypeLastCell).Column Lastcolumn = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column .Cells(Lastrow, 1).Copy .Range(.Cells(1, Lastcolumn + 1), .Cells(Lastrow, Lastcolumn + 1)).PasteSpecial Paste:=xlPasteAll, Transpose:=True End With Next ws On Error GoTo 0 End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding last column not always working correctly
This should find the last column in each sheet:
Sub FindLastColumn() Dim ncl As Long ncl = Columns.Count Dim ws As Worksheet For Each ws In Worksheets With ws For i = ncl To 1 Step -1 If Application.WorksheetFunction.CountA(.Columns(i)) < 0 Then MsgBox (ws.Name & " column " & i & " is last") GoTo nextsheet End If Next MsgBox ("Worksheet is empty") nextsheet: End With Next End Sub -- Gary''s Student - gsnu200907 "Sheela" wrote: I have a macro to find a last column in all the worksheets in a workbook. In some sheets it is correctly selecting the last column, but in some sheets it is passing the last column and selecting some other column on way right to it. I tried to use the following two methods, both are working the same way. Could someone figure out the correct way to find the last column? Public Sub test() Dim ws As Worksheet Dim Lastrow, Lastcolumn As Long On Error Resume Next For Each ws In ActiveWorkbook.Worksheets With ws Lastrow = .Range("A" & Rows.Count).End(xlUp).Row 'Lastcolumn = ActiveCell.SpecialCells(xlCellTypeLastCell).Column Lastcolumn = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column .Cells(Lastrow, 1).Copy .Range(.Cells(1, Lastcolumn + 1), .Cells(Lastrow, Lastcolumn + 1)).PasteSpecial Paste:=xlPasteAll, Transpose:=True End With Next ws On Error GoTo 0 End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding last column not always working correctly
Are you looking to locate the last displayed value in Row 2 or the last cell
in Row 2 with anything in it? That last part refers to formulas that are currently displaying the empty string ("")... the uncommented Lastcolumn statement finds the last cell with anything in it (a value OR a formula even if that formula is displaying the empty string). If you are after the column with the last displayed value in it (even if there cells after it with formulas currently displaying empty strings), then use this statement instead... Lastcolumn = ActiveSheet.Rows(2).Find(What:="*", LookIn:=xlValues, _ SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column -- Rick (MVP - Excel) "Sheela" wrote in message ... I have a macro to find a last column in all the worksheets in a workbook. In some sheets it is correctly selecting the last column, but in some sheets it is passing the last column and selecting some other column on way right to it. I tried to use the following two methods, both are working the same way. Could someone figure out the correct way to find the last column? Public Sub test() Dim ws As Worksheet Dim Lastrow, Lastcolumn As Long On Error Resume Next For Each ws In ActiveWorkbook.Worksheets With ws Lastrow = .Range("A" & Rows.Count).End(xlUp).Row 'Lastcolumn = ActiveCell.SpecialCells(xlCellTypeLastCell).Column Lastcolumn = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column .Cells(Lastrow, 1).Copy .Range(.Cells(1, Lastcolumn + 1), .Cells(Lastrow, Lastcolumn + 1)).PasteSpecial Paste:=xlPasteAll, Transpose:=True End With Next ws On Error GoTo 0 End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding last column not always working correctly
Thank you all for your responses.
Ryan: I tried to see whether there are any hidden characters or anything with the Ctrl+End, there were no hidden characters. the cursor always stopped at the last column. Rick, I used your function and realized that it calculates the lastcolumn in the first sheet, and always using the same number as lastcolumn in all other sheets. somehow the Lastcolumn is not being calculated in each sheet. Could you see my code in the original post and advise me ? thank you very much in advance, Sheela "Rick Rothstein" wrote: Are you looking to locate the last displayed value in Row 2 or the last cell in Row 2 with anything in it? That last part refers to formulas that are currently displaying the empty string ("")... the uncommented Lastcolumn statement finds the last cell with anything in it (a value OR a formula even if that formula is displaying the empty string). If you are after the column with the last displayed value in it (even if there cells after it with formulas currently displaying empty strings), then use this statement instead... Lastcolumn = ActiveSheet.Rows(2).Find(What:="*", LookIn:=xlValues, _ SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column -- Rick (MVP - Excel) "Sheela" wrote in message ... I have a macro to find a last column in all the worksheets in a workbook. In some sheets it is correctly selecting the last column, but in some sheets it is passing the last column and selecting some other column on way right to it. I tried to use the following two methods, both are working the same way. Could someone figure out the correct way to find the last column? Public Sub test() Dim ws As Worksheet Dim Lastrow, Lastcolumn As Long On Error Resume Next For Each ws In ActiveWorkbook.Worksheets With ws Lastrow = .Range("A" & Rows.Count).End(xlUp).Row 'Lastcolumn = ActiveCell.SpecialCells(xlCellTypeLastCell).Column Lastcolumn = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column .Cells(Lastrow, 1).Copy .Range(.Cells(1, Lastcolumn + 1), .Cells(Lastrow, Lastcolumn + 1)).PasteSpecial Paste:=xlPasteAll, Transpose:=True End With Next ws On Error GoTo 0 End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding last column not always working correctly
Here is your original code, modified to use the statement I posted
originally (notice, the only change I made was to remove the ActiveSheet reference and replace it with ws which is the variable name you used to iterate through the worksheets). Public Sub test() Dim ws As Worksheet Dim Lastrow, Lastcolumn As Long On Error Resume Next For Each ws In ActiveWorkbook.Worksheets With ws Lastrow = .Range("A" & Rows.Count).End(xlUp).Row Lastcolumn = ws.Rows(2).Find(What:="*", LookIn:=xlValues, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column .Cells(Lastrow, 1).Copy .Range(.Cells(1, Lastcolumn + 1), .Cells(Lastrow, Lastcolumn + 1)). _ PasteSpecial Paste:=xlPasteAll, Transpose:=True End With Next ws On Error GoTo 0 End Sub -- Rick (MVP - Excel) "Sheela" wrote in message ... Thank you all for your responses. Ryan: I tried to see whether there are any hidden characters or anything with the Ctrl+End, there were no hidden characters. the cursor always stopped at the last column. Rick, I used your function and realized that it calculates the lastcolumn in the first sheet, and always using the same number as lastcolumn in all other sheets. somehow the Lastcolumn is not being calculated in each sheet. Could you see my code in the original post and advise me ? thank you very much in advance, Sheela "Rick Rothstein" wrote: Are you looking to locate the last displayed value in Row 2 or the last cell in Row 2 with anything in it? That last part refers to formulas that are currently displaying the empty string ("")... the uncommented Lastcolumn statement finds the last cell with anything in it (a value OR a formula even if that formula is displaying the empty string). If you are after the column with the last displayed value in it (even if there cells after it with formulas currently displaying empty strings), then use this statement instead... Lastcolumn = ActiveSheet.Rows(2).Find(What:="*", LookIn:=xlValues, _ SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column -- Rick (MVP - Excel) "Sheela" wrote in message ... I have a macro to find a last column in all the worksheets in a workbook. In some sheets it is correctly selecting the last column, but in some sheets it is passing the last column and selecting some other column on way right to it. I tried to use the following two methods, both are working the same way. Could someone figure out the correct way to find the last column? Public Sub test() Dim ws As Worksheet Dim Lastrow, Lastcolumn As Long On Error Resume Next For Each ws In ActiveWorkbook.Worksheets With ws Lastrow = .Range("A" & Rows.Count).End(xlUp).Row 'Lastcolumn = ActiveCell.SpecialCells(xlCellTypeLastCell).Column Lastcolumn = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column .Cells(Lastrow, 1).Copy .Range(.Cells(1, Lastcolumn + 1), .Cells(Lastrow, Lastcolumn + 1)).PasteSpecial Paste:=xlPasteAll, Transpose:=True End With Next ws On Error GoTo 0 End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding last column not always working correctly
Thank you very much, Rick. It is working the way it is expected. but I need to change my code to find the last column, not just in row 2. Is there a way to find the last column in a worksheet in any row? Thank you again. Sheela "Rick Rothstein" wrote: Here is your original code, modified to use the statement I posted originally (notice, the only change I made was to remove the ActiveSheet reference and replace it with ws which is the variable name you used to iterate through the worksheets). Public Sub test() Dim ws As Worksheet Dim Lastrow, Lastcolumn As Long On Error Resume Next For Each ws In ActiveWorkbook.Worksheets With ws Lastrow = .Range("A" & Rows.Count).End(xlUp).Row Lastcolumn = ws.Rows(2).Find(What:="*", LookIn:=xlValues, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column .Cells(Lastrow, 1).Copy .Range(.Cells(1, Lastcolumn + 1), .Cells(Lastrow, Lastcolumn + 1)). _ PasteSpecial Paste:=xlPasteAll, Transpose:=True End With Next ws On Error GoTo 0 End Sub -- Rick (MVP - Excel) "Sheela" wrote in message ... Thank you all for your responses. Ryan: I tried to see whether there are any hidden characters or anything with the Ctrl+End, there were no hidden characters. the cursor always stopped at the last column. Rick, I used your function and realized that it calculates the lastcolumn in the first sheet, and always using the same number as lastcolumn in all other sheets. somehow the Lastcolumn is not being calculated in each sheet. Could you see my code in the original post and advise me ? thank you very much in advance, Sheela "Rick Rothstein" wrote: Are you looking to locate the last displayed value in Row 2 or the last cell in Row 2 with anything in it? That last part refers to formulas that are currently displaying the empty string ("")... the uncommented Lastcolumn statement finds the last cell with anything in it (a value OR a formula even if that formula is displaying the empty string). If you are after the column with the last displayed value in it (even if there cells after it with formulas currently displaying empty strings), then use this statement instead... Lastcolumn = ActiveSheet.Rows(2).Find(What:="*", LookIn:=xlValues, _ SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column -- Rick (MVP - Excel) "Sheela" wrote in message ... I have a macro to find a last column in all the worksheets in a workbook. In some sheets it is correctly selecting the last column, but in some sheets it is passing the last column and selecting some other column on way right to it. I tried to use the following two methods, both are working the same way. Could someone figure out the correct way to find the last column? Public Sub test() Dim ws As Worksheet Dim Lastrow, Lastcolumn As Long On Error Resume Next For Each ws In ActiveWorkbook.Worksheets With ws Lastrow = .Range("A" & Rows.Count).End(xlUp).Row 'Lastcolumn = ActiveCell.SpecialCells(xlCellTypeLastCell).Column Lastcolumn = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column .Cells(Lastrow, 1).Copy .Range(.Cells(1, Lastcolumn + 1), .Cells(Lastrow, Lastcolumn + 1)).PasteSpecial Paste:=xlPasteAll, Transpose:=True End With Next ws On Error GoTo 0 End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding last column not always working correctly
I am sorry I posted it to soon. I took out the rownumber (2) then it is
working perfect. Have a wonderful day. Sheela. "Sheela" wrote: Thank you very much, Rick. It is working the way it is expected. but I need to change my code to find the last column, not just in row 2. Is there a way to find the last column in a worksheet in any row? Thank you again. Sheela "Rick Rothstein" wrote: Here is your original code, modified to use the statement I posted originally (notice, the only change I made was to remove the ActiveSheet reference and replace it with ws which is the variable name you used to iterate through the worksheets). Public Sub test() Dim ws As Worksheet Dim Lastrow, Lastcolumn As Long On Error Resume Next For Each ws In ActiveWorkbook.Worksheets With ws Lastrow = .Range("A" & Rows.Count).End(xlUp).Row Lastcolumn = ws.Rows(2).Find(What:="*", LookIn:=xlValues, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column .Cells(Lastrow, 1).Copy .Range(.Cells(1, Lastcolumn + 1), .Cells(Lastrow, Lastcolumn + 1)). _ PasteSpecial Paste:=xlPasteAll, Transpose:=True End With Next ws On Error GoTo 0 End Sub -- Rick (MVP - Excel) "Sheela" wrote in message ... Thank you all for your responses. Ryan: I tried to see whether there are any hidden characters or anything with the Ctrl+End, there were no hidden characters. the cursor always stopped at the last column. Rick, I used your function and realized that it calculates the lastcolumn in the first sheet, and always using the same number as lastcolumn in all other sheets. somehow the Lastcolumn is not being calculated in each sheet. Could you see my code in the original post and advise me ? thank you very much in advance, Sheela "Rick Rothstein" wrote: Are you looking to locate the last displayed value in Row 2 or the last cell in Row 2 with anything in it? That last part refers to formulas that are currently displaying the empty string ("")... the uncommented Lastcolumn statement finds the last cell with anything in it (a value OR a formula even if that formula is displaying the empty string). If you are after the column with the last displayed value in it (even if there cells after it with formulas currently displaying empty strings), then use this statement instead... Lastcolumn = ActiveSheet.Rows(2).Find(What:="*", LookIn:=xlValues, _ SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column -- Rick (MVP - Excel) "Sheela" wrote in message ... I have a macro to find a last column in all the worksheets in a workbook. In some sheets it is correctly selecting the last column, but in some sheets it is passing the last column and selecting some other column on way right to it. I tried to use the following two methods, both are working the same way. Could someone figure out the correct way to find the last column? Public Sub test() Dim ws As Worksheet Dim Lastrow, Lastcolumn As Long On Error Resume Next For Each ws In ActiveWorkbook.Worksheets With ws Lastrow = .Range("A" & Rows.Count).End(xlUp).Row 'Lastcolumn = ActiveCell.SpecialCells(xlCellTypeLastCell).Column Lastcolumn = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column .Cells(Lastrow, 1).Copy .Range(.Cells(1, Lastcolumn + 1), .Cells(Lastrow, Lastcolumn + 1)).PasteSpecial Paste:=xlPasteAll, Transpose:=True End With Next ws On Error GoTo 0 End Sub |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding last column not always working correctly
Try this code instead...
Public Sub test() Dim ws As Worksheet Dim Lastrow, Lastcolumn As Long On Error Resume Next For Each ws In ActiveWorkbook.Worksheets With ws Lastrow = .Range("A" & Rows.Count).End(xlUp).Row Lastcolumn = ws.Cells.Find(What:="*", SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, LookIn:=xlValues).Column .Cells(Lastrow, 1).Copy .Range(.Cells(1, Lastcolumn + 1), .Cells(Lastrow, Lastcolumn + 1)). _ PasteSpecial Paste:=xlPasteAll, Transpose:=True End With Next ws On Error GoTo 0 End Sub -- Rick (MVP - Excel) "Sheela" wrote in message ... Thank you very much, Rick. It is working the way it is expected. but I need to change my code to find the last column, not just in row 2. Is there a way to find the last column in a worksheet in any row? Thank you again. Sheela "Rick Rothstein" wrote: Here is your original code, modified to use the statement I posted originally (notice, the only change I made was to remove the ActiveSheet reference and replace it with ws which is the variable name you used to iterate through the worksheets). Public Sub test() Dim ws As Worksheet Dim Lastrow, Lastcolumn As Long On Error Resume Next For Each ws In ActiveWorkbook.Worksheets With ws Lastrow = .Range("A" & Rows.Count).End(xlUp).Row Lastcolumn = ws.Rows(2).Find(What:="*", LookIn:=xlValues, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column .Cells(Lastrow, 1).Copy .Range(.Cells(1, Lastcolumn + 1), .Cells(Lastrow, Lastcolumn + 1)). _ PasteSpecial Paste:=xlPasteAll, Transpose:=True End With Next ws On Error GoTo 0 End Sub -- Rick (MVP - Excel) "Sheela" wrote in message ... Thank you all for your responses. Ryan: I tried to see whether there are any hidden characters or anything with the Ctrl+End, there were no hidden characters. the cursor always stopped at the last column. Rick, I used your function and realized that it calculates the lastcolumn in the first sheet, and always using the same number as lastcolumn in all other sheets. somehow the Lastcolumn is not being calculated in each sheet. Could you see my code in the original post and advise me ? thank you very much in advance, Sheela "Rick Rothstein" wrote: Are you looking to locate the last displayed value in Row 2 or the last cell in Row 2 with anything in it? That last part refers to formulas that are currently displaying the empty string ("")... the uncommented Lastcolumn statement finds the last cell with anything in it (a value OR a formula even if that formula is displaying the empty string). If you are after the column with the last displayed value in it (even if there cells after it with formulas currently displaying empty strings), then use this statement instead... Lastcolumn = ActiveSheet.Rows(2).Find(What:="*", LookIn:=xlValues, _ SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column -- Rick (MVP - Excel) "Sheela" wrote in message ... I have a macro to find a last column in all the worksheets in a workbook. In some sheets it is correctly selecting the last column, but in some sheets it is passing the last column and selecting some other column on way right to it. I tried to use the following two methods, both are working the same way. Could someone figure out the correct way to find the last column? Public Sub test() Dim ws As Worksheet Dim Lastrow, Lastcolumn As Long On Error Resume Next For Each ws In ActiveWorkbook.Worksheets With ws Lastrow = .Range("A" & Rows.Count).End(xlUp).Row 'Lastcolumn = ActiveCell.SpecialCells(xlCellTypeLastCell).Column Lastcolumn = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column .Cells(Lastrow, 1).Copy .Range(.Cells(1, Lastcolumn + 1), .Cells(Lastrow, Lastcolumn + 1)).PasteSpecial Paste:=xlPasteAll, Transpose:=True End With Next ws On Error GoTo 0 End Sub |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding last column not always working correctly
Thank you very much. "Rick Rothstein" wrote: Try this code instead... Public Sub test() Dim ws As Worksheet Dim Lastrow, Lastcolumn As Long On Error Resume Next For Each ws In ActiveWorkbook.Worksheets With ws Lastrow = .Range("A" & Rows.Count).End(xlUp).Row Lastcolumn = ws.Cells.Find(What:="*", SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, LookIn:=xlValues).Column .Cells(Lastrow, 1).Copy .Range(.Cells(1, Lastcolumn + 1), .Cells(Lastrow, Lastcolumn + 1)). _ PasteSpecial Paste:=xlPasteAll, Transpose:=True End With Next ws On Error GoTo 0 End Sub -- Rick (MVP - Excel) "Sheela" wrote in message ... Thank you very much, Rick. It is working the way it is expected. but I need to change my code to find the last column, not just in row 2. Is there a way to find the last column in a worksheet in any row? Thank you again. Sheela "Rick Rothstein" wrote: Here is your original code, modified to use the statement I posted originally (notice, the only change I made was to remove the ActiveSheet reference and replace it with ws which is the variable name you used to iterate through the worksheets). Public Sub test() Dim ws As Worksheet Dim Lastrow, Lastcolumn As Long On Error Resume Next For Each ws In ActiveWorkbook.Worksheets With ws Lastrow = .Range("A" & Rows.Count).End(xlUp).Row Lastcolumn = ws.Rows(2).Find(What:="*", LookIn:=xlValues, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column .Cells(Lastrow, 1).Copy .Range(.Cells(1, Lastcolumn + 1), .Cells(Lastrow, Lastcolumn + 1)). _ PasteSpecial Paste:=xlPasteAll, Transpose:=True End With Next ws On Error GoTo 0 End Sub -- Rick (MVP - Excel) "Sheela" wrote in message ... Thank you all for your responses. Ryan: I tried to see whether there are any hidden characters or anything with the Ctrl+End, there were no hidden characters. the cursor always stopped at the last column. Rick, I used your function and realized that it calculates the lastcolumn in the first sheet, and always using the same number as lastcolumn in all other sheets. somehow the Lastcolumn is not being calculated in each sheet. Could you see my code in the original post and advise me ? thank you very much in advance, Sheela "Rick Rothstein" wrote: Are you looking to locate the last displayed value in Row 2 or the last cell in Row 2 with anything in it? That last part refers to formulas that are currently displaying the empty string ("")... the uncommented Lastcolumn statement finds the last cell with anything in it (a value OR a formula even if that formula is displaying the empty string). If you are after the column with the last displayed value in it (even if there cells after it with formulas currently displaying empty strings), then use this statement instead... Lastcolumn = ActiveSheet.Rows(2).Find(What:="*", LookIn:=xlValues, _ SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column -- Rick (MVP - Excel) "Sheela" wrote in message ... I have a macro to find a last column in all the worksheets in a workbook. In some sheets it is correctly selecting the last column, but in some sheets it is passing the last column and selecting some other column on way right to it. I tried to use the following two methods, both are working the same way. Could someone figure out the correct way to find the last column? Public Sub test() Dim ws As Worksheet Dim Lastrow, Lastcolumn As Long On Error Resume Next For Each ws In ActiveWorkbook.Worksheets With ws Lastrow = .Range("A" & Rows.Count).End(xlUp).Row 'Lastcolumn = ActiveCell.SpecialCells(xlCellTypeLastCell).Column Lastcolumn = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column .Cells(Lastrow, 1).Copy .Range(.Cells(1, Lastcolumn + 1), .Cells(Lastrow, Lastcolumn + 1)).PasteSpecial Paste:=xlPasteAll, Transpose:=True End With Next ws On Error GoTo 0 End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AutoSum not working correctly | Excel Discussion (Misc queries) | |||
VLookup is not working correctly | Excel Worksheet Functions | |||
Code not working correctly | Excel Programming | |||
This is not working because I didn't write it correctly | Excel Programming | |||
Column find code not working correctly | Excel Programming |