Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting blank rows with formulas in them
I have a spreadsheet that takes data from one worksheet and moves it to
several worksheets, depending on a certain value. There are several worksheets where the data has been moved, based on a macro and formula I wrote. There are several rows that will always be blank, however a formula appears in those rows. Is there a way to create a macro that will look at all the "named" sheets individually and find any rows from 8-300 that have a formula in them, but do not contain actual data. For instance, worksheet NEO has formulas that feed off another sheet from row 8-300. However, only data appears in rows 8-125. Rows 126-300 are blank but contain a formula. How can that e deleted. Same thing with worksheet OEN, except data is present in rows 8-100, with 101-300 being blank but containint formulas. Same with other worksheets. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting blank rows with formulas in them
Hi,
Which columns are we looking at in these rows, all of them? -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Kennedy" wrote: I have a spreadsheet that takes data from one worksheet and moves it to several worksheets, depending on a certain value. There are several worksheets where the data has been moved, based on a macro and formula I wrote. There are several rows that will always be blank, however a formula appears in those rows. Is there a way to create a macro that will look at all the "named" sheets individually and find any rows from 8-300 that have a formula in them, but do not contain actual data. For instance, worksheet NEO has formulas that feed off another sheet from row 8-300. However, only data appears in rows 8-125. Rows 126-300 are blank but contain a formula. How can that e deleted. Same thing with worksheet OEN, except data is present in rows 8-100, with 101-300 being blank but containint formulas. Same with other worksheets. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting blank rows with formulas in them
You can try out the below macro. If you are new to macros..
--Set the Security level to low/medium in (Tools|Macro|Security). --From workbook launch VBE using short-key Alt+F11. --From menu 'Insert' a module and paste the below code. --Get back to Workbook. --Run macro from Tools|Macro|Run <selected macro() Namedrange "SheetNames"..Change to suit Sub DeleteRows() Dim ws As Worksheet, cell As Range, lngRow As Long For Each cell In Range("SheetNames") Set ws = Sheets(cell.Text) For lngRow = 24 To 8 Step -1 If WorksheetFunction.CountBlank(ws.Rows(lngRow)) = Columns.Count _ Then ws.Rows(lngRow).Delete Next Next End Sub -- Jacob (MVP - Excel) "Kennedy" wrote: I have a spreadsheet that takes data from one worksheet and moves it to several worksheets, depending on a certain value. There are several worksheets where the data has been moved, based on a macro and formula I wrote. There are several rows that will always be blank, however a formula appears in those rows. Is there a way to create a macro that will look at all the "named" sheets individually and find any rows from 8-300 that have a formula in them, but do not contain actual data. For instance, worksheet NEO has formulas that feed off another sheet from row 8-300. However, only data appears in rows 8-125. Rows 126-300 are blank but contain a formula. How can that e deleted. Same thing with worksheet OEN, except data is present in rows 8-100, with 101-300 being blank but containint formulas. Same with other worksheets. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting blank rows with formulas in them
Mike,
All Columns. In this case, I have formulas in rows 8-300, columns A-P. So if the formulas returns data back in rows 8-100, columns A-P, keep that, but delete all formulas from 101-300, columns A-P. The one thing consistent in all worksheets is that the data starts on row 8, and use columns A-P "Mike H" wrote: Hi, Which columns are we looking at in these rows, all of them? -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Kennedy" wrote: I have a spreadsheet that takes data from one worksheet and moves it to several worksheets, depending on a certain value. There are several worksheets where the data has been moved, based on a macro and formula I wrote. There are several rows that will always be blank, however a formula appears in those rows. Is there a way to create a macro that will look at all the "named" sheets individually and find any rows from 8-300 that have a formula in them, but do not contain actual data. For instance, worksheet NEO has formulas that feed off another sheet from row 8-300. However, only data appears in rows 8-125. Rows 126-300 are blank but contain a formula. How can that e deleted. Same thing with worksheet OEN, except data is present in rows 8-100, with 101-300 being blank but containint formulas. Same with other worksheets. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting blank rows with formulas in them
Jacob,
Maybe I did something wrong. Changed the "SheetNames" to "GEO", which is one of the worksheets that I have. Tried running the macro and returned an error: Run-time error '1004': Method 'Range' of object'_Global' failed. When looking at the macro, the For Each cell In Range("GEO") is highlighted in yellow "Jacob Skaria" wrote: You can try out the below macro. If you are new to macros.. --Set the Security level to low/medium in (Tools|Macro|Security). --From workbook launch VBE using short-key Alt+F11. --From menu 'Insert' a module and paste the below code. --Get back to Workbook. --Run macro from Tools|Macro|Run <selected macro() Namedrange "SheetNames"..Change to suit Sub DeleteRows() Dim ws As Worksheet, cell As Range, lngRow As Long For Each cell In Range("SheetNames") Set ws = Sheets(cell.Text) For lngRow = 24 To 8 Step -1 If WorksheetFunction.CountBlank(ws.Rows(lngRow)) = Columns.Count _ Then ws.Rows(lngRow).Delete Next Next End Sub -- Jacob (MVP - Excel) "Kennedy" wrote: I have a spreadsheet that takes data from one worksheet and moves it to several worksheets, depending on a certain value. There are several worksheets where the data has been moved, based on a macro and formula I wrote. There are several rows that will always be blank, however a formula appears in those rows. Is there a way to create a macro that will look at all the "named" sheets individually and find any rows from 8-300 that have a formula in them, but do not contain actual data. For instance, worksheet NEO has formulas that feed off another sheet from row 8-300. However, only data appears in rows 8-125. Rows 126-300 are blank but contain a formula. How can that e deleted. Same thing with worksheet OEN, except data is present in rows 8-100, with 101-300 being blank but containint formulas. Same with other worksheets. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting blank rows with formulas in them
Try this,
Change the list of sheet names to the ones you want to work on Sub marine() Dim ws As Worksheet Dim MyRange As Range Dim CopyRange As Range S = "Sheet1,Sheet2,Sheet9" 'Change to suit V = Split(S, ",") For Each ws In ThisWorkbook.Worksheets If Not IsError(Application.Match(ws.Name, V, 0)) Then Set MyRange = ws.Range("A8:A800") For Each c In MyRange If WorksheetFunction.Count(Range("A" & c.Row & ":" & "P" & c.Row)) = 0 Then If CopyRange Is Nothing Then Set CopyRange = c.EntireRow Else Set CopyRange = Union(CopyRange, c.EntireRow) End If End If Next End If If Not CopyRange Is Nothing Then CopyRange.Delete Set CopyRange = Nothing End If Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Kennedy" wrote: Mike, All Columns. In this case, I have formulas in rows 8-300, columns A-P. So if the formulas returns data back in rows 8-100, columns A-P, keep that, but delete all formulas from 101-300, columns A-P. The one thing consistent in all worksheets is that the data starts on row 8, and use columns A-P "Mike H" wrote: Hi, Which columns are we looking at in these rows, all of them? -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Kennedy" wrote: I have a spreadsheet that takes data from one worksheet and moves it to several worksheets, depending on a certain value. There are several worksheets where the data has been moved, based on a macro and formula I wrote. There are several rows that will always be blank, however a formula appears in those rows. Is there a way to create a macro that will look at all the "named" sheets individually and find any rows from 8-300 that have a formula in them, but do not contain actual data. For instance, worksheet NEO has formulas that feed off another sheet from row 8-300. However, only data appears in rows 8-125. Rows 126-300 are blank but contain a formula. How can that e deleted. Same thing with worksheet OEN, except data is present in rows 8-100, with 101-300 being blank but containint formulas. Same with other worksheets. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting blank rows with formulas in them
Hi Kennedy
"Sheetnames" is a named range containing the sheet names you want to delete.. If you are looking to delete just one sheet then try the below version... Sub DeleteRows() Dim ws As Worksheet, lngRow As Long Set ws = Sheets("GEO) For lngRow = 24 To 8 Step -1 If WorksheetFunction.CountBlank(ws.Rows(lngRow)) = Columns.Count _ Then ws.Rows(lngRow).Delete Next End Sub -- Jacob (MVP - Excel) "Kennedy" wrote: Jacob, Maybe I did something wrong. Changed the "SheetNames" to "GEO", which is one of the worksheets that I have. Tried running the macro and returned an error: Run-time error '1004': Method 'Range' of object'_Global' failed. When looking at the macro, the For Each cell In Range("GEO") is highlighted in yellow "Jacob Skaria" wrote: You can try out the below macro. If you are new to macros.. --Set the Security level to low/medium in (Tools|Macro|Security). --From workbook launch VBE using short-key Alt+F11. --From menu 'Insert' a module and paste the below code. --Get back to Workbook. --Run macro from Tools|Macro|Run <selected macro() Namedrange "SheetNames"..Change to suit Sub DeleteRows() Dim ws As Worksheet, cell As Range, lngRow As Long For Each cell In Range("SheetNames") Set ws = Sheets(cell.Text) For lngRow = 24 To 8 Step -1 If WorksheetFunction.CountBlank(ws.Rows(lngRow)) = Columns.Count _ Then ws.Rows(lngRow).Delete Next Next End Sub -- Jacob (MVP - Excel) "Kennedy" wrote: I have a spreadsheet that takes data from one worksheet and moves it to several worksheets, depending on a certain value. There are several worksheets where the data has been moved, based on a macro and formula I wrote. There are several rows that will always be blank, however a formula appears in those rows. Is there a way to create a macro that will look at all the "named" sheets individually and find any rows from 8-300 that have a formula in them, but do not contain actual data. For instance, worksheet NEO has formulas that feed off another sheet from row 8-300. However, only data appears in rows 8-125. Rows 126-300 are blank but contain a formula. How can that e deleted. Same thing with worksheet OEN, except data is present in rows 8-100, with 101-300 being blank but containint formulas. Same with other worksheets. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting blank rows with formulas in them
oops,,
There's a bug in that, try this instead Sub marine() Dim ws As Worksheet Dim MyRange As Range Dim CopyRange As Range S = "Sheet1,Sheet2,Sheet9" 'Change to suit V = Split(S, ",") For Each ws In ThisWorkbook.Worksheets If Not IsError(Application.Match(ws.Name, V, 0)) Then Set MyRange = ws.Range("A8:A800" & lastrow) For Each c In MyRange If WorksheetFunction.Count(ws.Range("A" & c.Row & ":" & "P" & c.Row)) = 0 Then If CopyRange Is Nothing Then Set CopyRange = c.EntireRow Else Set CopyRange = Union(CopyRange, c.EntireRow) End If End If Next End If If Not CopyRange Is Nothing Then CopyRange.Delete Set CopyRange = Nothing End If Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Mike H" wrote: Try this, Change the list of sheet names to the ones you want to work on Sub marine() Dim ws As Worksheet Dim MyRange As Range Dim CopyRange As Range S = "Sheet1,Sheet2,Sheet9" 'Change to suit V = Split(S, ",") For Each ws In ThisWorkbook.Worksheets If Not IsError(Application.Match(ws.Name, V, 0)) Then Set MyRange = ws.Range("A8:A800") For Each c In MyRange If WorksheetFunction.Count(Range("A" & c.Row & ":" & "P" & c.Row)) = 0 Then If CopyRange Is Nothing Then Set CopyRange = c.EntireRow Else Set CopyRange = Union(CopyRange, c.EntireRow) End If End If Next End If If Not CopyRange Is Nothing Then CopyRange.Delete Set CopyRange = Nothing End If Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Kennedy" wrote: Mike, All Columns. In this case, I have formulas in rows 8-300, columns A-P. So if the formulas returns data back in rows 8-100, columns A-P, keep that, but delete all formulas from 101-300, columns A-P. The one thing consistent in all worksheets is that the data starts on row 8, and use columns A-P "Mike H" wrote: Hi, Which columns are we looking at in these rows, all of them? -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Kennedy" wrote: I have a spreadsheet that takes data from one worksheet and moves it to several worksheets, depending on a certain value. There are several worksheets where the data has been moved, based on a macro and formula I wrote. There are several rows that will always be blank, however a formula appears in those rows. Is there a way to create a macro that will look at all the "named" sheets individually and find any rows from 8-300 that have a formula in them, but do not contain actual data. For instance, worksheet NEO has formulas that feed off another sheet from row 8-300. However, only data appears in rows 8-125. Rows 126-300 are blank but contain a formula. How can that e deleted. Same thing with worksheet OEN, except data is present in rows 8-100, with 101-300 being blank but containint formulas. Same with other worksheets. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need Help Deleting Blank Rows | Excel Worksheet Functions | |||
deleting blank rows | New Users to Excel | |||
Deleting Blank Rows. | Excel Discussion (Misc queries) | |||
Deleting blank rows | Excel Worksheet Functions | |||
Deleting Blank Rows | New Users to Excel |