Posts by dsohng

"You can't paste this here because the Copy area and paste area aren't the same size. Select just one cell in the paste area or an area that's the same size, and try pasting again." This error msg after I unmerged cells which was the first error msg "We can't do that to a merged cell." So my sheet, owned by another user, has merged cells, hidden columns and filtered data. This trifecta makes pasting values over formulas a task from hell.

Or maybe a vba code to unhide and unfilter all, and then to re-hide and re-filter after paste special values.

Hi Roy - no, if I could, I'd paste special values the entire sheet. Thanks for looking into this.

And to further complicate things, it looks like some of the rows may be hidden as well.

FYI, It's fine to copy paste values over the values in the hidden columns.

Hi Everyone,

I'm trying to overwrite formulas with their values in my range (B8:Z200). My range contains hidden columns and Excel will not allow me to copy paste values using visible cells only (error: This action won't work on multiple selections). The only way is to copy paste values over each visible column one at a time. As I am not familiar with looping, is there a code to do this? Any help would be appreciated. Thanks.

Thank you NBVC. This worked perfectly.

Hi Folks,

For the offset formula below that I copied multiple times downward in a column, is there any way I can write the row number increase as a formula? Any help would be appreciated. Thanks.

=OFFSET(B10,-6,-7)

=OFFSET(B10,-7,-7)

=OFFSET(B10,-8,-7)

=OFFSET(B10,-9,-7)

=OFFSET(B10,-10,-7)

Hi Luke,

Thanks for responding. Essentially for all the boldfaced data points in the table, I need to list out next to it all its attributes from the first column, last two columns, and the top row. See the one sample row of "1770" at the bottom. I figured if I could get the cell address of the data point, I could use an index/match to get all the attributes, Thanks.

[TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[tr]

[TD="width: 89"] [/TD]
[TD="width: 61"]40[/TD]
[TD="width: 61"]45[/TD]
[TD="width: 61"]50[/TD]
[TD="width: 61"]55[/TD]
[TD="width: 61"]60[/TD]
[TD="width: 61"]65[/TD]
[TD="width: 61"]70[/TD]
[TD="width: 61"]75[/TD]
[TD="width: 61"]80[/TD]
[TD="width: 61"]85[/TD]
[TD="width: 61"]90[/TD]
[TD="width: 61"] [/TD]
[TD="width: 61"] [/TD]

[/tr]

[tr]

[TD="width: 89"]2.00%[/TD]
[TD="width: 61"]1770[/TD]
[TD="width: 61"]1796[/TD]
[TD="width: 61"]1821[/TD]
[TD="width: 61"]1831[/TD]
[TD="width: 61"]1852[/TD]
[TD="width: 61"]1872[/TD]
[TD="width: 61"]1885[/TD]
[TD="width: 61"]1897[/TD]
[TD="width: 61"]1902[/TD]
[TD="width: 61"]1913[/TD]
[TD="width: 61"]1918[/TD]

[td]

0.04%

[/td]

[TD="width: 61"]2[/TD]

[/tr]

[tr]

[TD="width: 89"]2.10%[/TD]
[TD="width: 61"]1835[/TD]
[TD="width: 61"]1860[/TD]
[TD="width: 61"]1882[/TD]
[TD="width: 61"]1909[/TD]
[TD="width: 61"]1927[/TD]
[TD="width: 61"]1940[/TD]
[TD="width: 61"]1952[/TD]
[TD="width: 61"]1965[/TD]
[TD="width: 61"]1979[/TD]
[TD="width: 61"]1984[/TD]
[TD="width: 61"]1992[/TD]

[td]

0.05%

[/td]

[TD="width: 61"]4[/TD]

[/tr]

[tr]

[TD="width: 89"]2.20%[/TD]
[TD="width: 61"]1902[/TD]
[TD="width: 61"]1934[/TD]
[TD="width: 61"]1959[/TD]
[TD="width: 61"]1977[/TD]
[TD="width: 61"]1996[/TD]
[TD="width: 61"]2009[/TD]
[TD="width: 61"]2026[/TD]
[TD="width: 61"]2038[/TD]
[TD="width: 61"]2043[/TD]
[TD="width: 61"]2057[/TD]
[TD="width: 61"]2062[/TD]

[td]

0.05%

[/td]

[TD="width: 61"]6[/TD]

[/tr]

[tr]

[TD="width: 89"]2.30%[/TD]
[TD="width: 61"]1971[/TD]
[TD="width: 61"]2002[/TD]
[TD="width: 61"]2027[/TD]
[TD="width: 61"]2046[/TD]
[TD="width: 61"]2065[/TD]
[TD="width: 61"]2084[/TD]
[TD="width: 61"]2096[/TD]
[TD="width: 61"]2106[/TD]
[TD="width: 61"]2119[/TD]
[TD="width: 61"]2124[/TD]
[TD="width: 61"]2136[/TD]

[td]

0.06%

[/td]

[TD="width: 61"]8[/TD]

[/tr]

[tr]

[TD="width: 89"]2.40%[/TD]
[TD="width: 61"]2035[/TD]
[TD="width: 61"]2071[/TD]
[TD="width: 61"]2096[/TD]
[TD="width: 61"]2115[/TD]
[TD="width: 61"]2138[/TD]
[TD="width: 61"]2152[/TD]
[TD="width: 61"]2165[/TD]
[TD="width: 61"]2174[/TD]
[TD="width: 61"]2187[/TD]
[TD="width: 61"]2201[/TD]
[TD="width: 61"]2206[/TD]

[td]

0.06%

[/td]

[TD="width: 61"]10[/TD]

[/tr]

[tr]

[TD="width: 89"]2.50%[/TD]
[TD="width: 61"]2101[/TD]
[TD="width: 61"]2140[/TD]
[TD="width: 61"]2165[/TD]
[TD="width: 61"]2190[/TD]
[TD="width: 61"]2209[/TD]
[TD="width: 61"]2221[/TD]
[TD="width: 61"]2231[/TD]
[TD="width: 61"]2249[/TD]
[TD="width: 61"]2263[/TD]
[TD="width: 61"]2268[/TD]
[TD="width: 61"]2280[/TD]

[td]

0.07%

[/td]

[TD="width: 61"]12[/TD]

[/tr]

[tr]

[TD="width: 89"]2.60%[/TD]
[TD="width: 61"]2174[/TD]
[TD="width: 61"]2206[/TD]
[TD="width: 61"]2229[/TD]
[TD="width: 61"]2256[/TD]
[TD="width: 61"]2275[/TD]
[TD="width: 61"]2293[/TD]
[TD="width: 61"]2306[/TD]
[TD="width: 61"]2323[/TD]
[TD="width: 61"]2335[/TD]
[TD="width: 61"]2340[/TD]
[TD="width: 61"]2352[/TD]

[td]

0.07%

[/td]

[TD="width: 61"]14[/TD]

[/tr]

[/TABLE]

[TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[tr]

[TD="width: 61"]1770[/TD]
[TD="width: 61"]2.00%[/TD]
[TD="width: 61"]40[/TD]
[TD="width: 61"]0.04%[/TD]
[TD="width: 61"]2[/TD]

[/tr]

[/TABLE]

Hello all,

I'm trying to find a formula to find the exact cell address for a value in a table (or named range).

The formula below does not work as the Match formula will only work for a row or column range. Does anyone know how to fix this? Thanks.

=CELL("address",INDEX("Named Range",MATCH("search value","Named Range",0),1))

Flawless. Thanks again Mumps.

I attempted adding rngAddress but the code failed.

Sub insertRows_GOLD()
Application.ScreenUpdating = False
Dim LastRow As Long
Dim rngAddress As Range
Set rngAddress = Range("A1:CC1").Find("Insert total Line Count")

LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Dim x As Long
For x = LastRow To 4 Step -1
Rows(x).EntireRow.Copy
Rows(x + 1).Resize(Cells(x, rngAddress).Value).Insert
Next x
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Hi Mumps - in your code below that selects Column "AG", how can I make the column selection by the header name (row 1) instead since the worksheet columns will vary from time to time. Thanks.

Sub insertRows()
Application.ScreenUpdating = False
Dim LastRow As Long
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Dim x As Long
For x = LastRow To 4 Step -1
Rows(x).EntireRow.Copy
Rows(x + 1).Resize(Cells(x, "AG").Value).Insert
Next x
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Works like a charm - perfectly succinct code. You guys are the best.

Following up with Gijsmo help with a previous code, I am trying to now copy row data to the empty rows beneath it up until the next row with data, and then repeat to the end of the data, Thanks.

Gijsmo -

It's strange that the code wouldn't work in my original spreadsheet. I had to copy my data to yours to get it to work. Nevertheless, thanks much for the help! Originally I also wanted to copy the row data down to its inserted lines for the entire spreadsheet. Any help on this last piece would me appreciated. Thanks again.

I aligned the data. I made it simple just to see if the code would work - just 2 columns starting with data in Col A with Col B = to the total count.

Const StartCol = 1
Const Offset = 2

The code still skips over:
.Rows(j + 1 & ":" & j + .Cells(j, StartCol + Offset).Value).Insert Shift:=xlDown

Thanks for responding Gijsmo. When I run the code nothing happens. When I step thru, after running
For j = .Cells(.Rows.Count, 1).End(xlUp).Row To 2 Step -1 it skips the next line of code and goes directly to "End with". any suggestions? Thanks.

Hello everyone - junior programmer wanna be here who is ready for a Xanax. I am trying to insert X number of rows below each visible row (X = Total Count column of that row). I want to copy the original row into the X number of rows and then repeat for all remaining rows. Any help would be greatly appreciated!!

[TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[tr]

[TD="width: 55"]Issuer[/TD]
[TD="width: 127"]Facility[/TD]
[TD="width: 78"]LoanX ID[/TD]
[TD="width: 60"]Price[/TD]
[TD="width: 53"]Depth[/TD]
[TD="width: 92"]Total Count[/TD]

[/tr]

[tr]

[TD="width: 55"]Issuer A[/TD]
[TD="width: 127"]Term Loan B[/TD]
[TD="width: 78"]LXAAAA1[/TD]
[TD="width: 60, align: right"]100.771[/TD]
[TD="width: 53"]LoanX 3[/TD]
[TD="width: 92"]2[/TD]

[/tr]

[tr]

[TD="width: 55"]Issuer B[/TD]
[TD="width: 127"]2017 Class B Term Loan[/TD]
[TD="width: 78"]LXAAAA2[/TD]
[TD="width: 60, align: right"]100.216[/TD]
[TD="width: 53"]LoanX 9[/TD]
[TD="width: 92"]2[/TD]

[/tr]

[tr]

[TD="width: 55"]Issuer C[/TD]
[TD="width: 127"]Term Loan B[/TD]
[TD="width: 78"]LXAAAA3[/TD]
[TD="width: 60, align: right"]100.625[/TD]
[TD="width: 53"]LoanX 2[/TD]
[TD="width: 92"]3[/TD]

[/tr]

[tr]

[TD="width: 55"]Issuer D[/TD]
[TD="width: 127"]Term Loan B-1[/TD]
[TD="width: 78"]LXAAAA4[/TD]
[TD="width: 60, align: right"]100.797[/TD]
[TD="width: 53"]LoanX 4[/TD]
[TD="width: 92"]4[/TD]

[/tr]

[tr]

[TD="width: 55"]Issuer E[/TD]
[TD="width: 127"]New Term Loan B[/TD]
[TD="width: 78"]LXAAAA5[/TD]
[TD="width: 60, align: right"]100.95[/TD]
[TD="width: 53"]LoanX 5[/TD]
[TD="width: 92"]6[/TD]

[/tr]

[/TABLE]

Sub Copy_Rows_Down()

Dim sh As Worksheet
Set sh = Worksheets("Sheet1")
Dim LastRow As Long
LastRow = Range("AA" & Rows.count).End(xlUp).Row

Dim totalTRADES As Range
Set totalTRADES = ActiveCell.Offset(0, 6).Select

Dim i As Long, j As Long

i = 3
Do While i <= LastRow

For j = 1 To totalTRADES
sh.Rows(i).Copy
sh.Rows(i).Offset(1).Insert Shift:=xlDown
Next j

i = i + totalTRADES + 1
LastRow = LastRow + totalDS

Loop